Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

assigning DB-user to server role

Hello NG,
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> Hello NG,
> each time we assign users to roles, we have to additionally grant each
> access right of each function of each table manually to this new user. Of
> course we'd expect this to be done by the assignment itself.
> Either we don't understand the servers's security philosophy correctly or
> there should be another procedure to grant users access to the database

---

via

quote:

---

> the role, which has already defined access rights.
> Thankful for any hint ...
> Marc
>

---

|||You script seems to promise what we expected when we used the Enterprise
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You should be able to grant object permissions to the roles only. Member
> users will inherit permissions via role membership. If a user is a member
> of multiple roles, granted permissions are cumulative. However, denied
> permissions take precedence.
> I'm not sure how you've setup your users but the script below illustrates
> this technique.
> USE MyDatabase
> --setup role security
> EXEC sp_addrole 'MyRole'
> GRANT SELECT ON MyTable TO MyRole
> --setup user security
> EXEC sp_grantlogin 'MyDomain\MyUser'
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
> news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
Of[QUOTE]
or[QUOTE]
> via
>

---

|||You should be able to do these same steps using EM as well. I find that SQL
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> You script seems to promise what we expected when we used the Enterprise
> Manager to assign users to roles. Why does it work through scripts and not
> through "klick-it"?
> Marc
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
> news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
Member[QUOTE]
member[QUOTE]
illustrates[QUOTE]
> Of
> or
database[QUOTE]
>

---

|||Perhaps if you used profiler while you recreate your scenario in EM (create
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

aspnetdb.mdf on Hosting Server ?

I have established roles to restrict access to the dataentry pages. The database is SQL Server which I've established on the hosting server. Security was previously maintained through~/app_data/aspnetdb.mdb (MS Access). It works, but isn't too stable, so I have modified the security to useSQL Server with an aspnetdb.mdf file, also located in the app_datafolder. Is this appropriate? Since it doesn't work, I assume not.

It's now my assumption that I need to setup a SQL Serverdatabase to support the security/roles. I have seen some reference tousing a script to build the aspnet.mdf on the hosting server. IfI do create such a database, is there anything special I need to do tohave my application read the aspnetdb? Do I need a connection stringin the membership and/or roleManager sections of my web.config.

A little guidance would be appreciated.

Thanks

http://www.eggheadcafe.com/articles/20060529.asp

|||

Thank you Dr. Bromberg for the extensive material you've pointed me to. I have downloaded the code and created a web site on my local Visual Studio server. I was able to run the SQL to create the two databases, Articles and aspnetdb (I think that's what I was suppose to do?). I have modifed the web.config to reflect the localhost information. When I run the Default.aspx I receive the following error:

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

OK, something's not setup properly. I attempted to run the SetupASPNetDatabase.aspx page and get absolutely nowhere, most likely because I have no idea what values to submit in the query boxes.

Bottom line is, I am not clear on what I need to do to get started. I am sure there is considerable information to glean from your article and sample web site. I would genuinely like to learn from it. The problem I seem to be having through is getting the security configured, which takes me back to square one with my initial question. I feel as if I am in the proverbial Catch 22. I need to get my own hosted security working and/or get your sample working before I can get either of them working.

|||

To try and answer your question about setting up security with SQL Server...

1. Install the security schema (tables, procedures etc) on the SQL Server database. You can do this on your local machine by running aspnetregsql.exe (it's an installation wizard which comes with the ASP.Net framwork). If you have full control on your hosted server, then install the same way, otherwise your hosting provider will have to assist (some offer the installation as an option via their Control Panel). Note that the database doesn't have to be called ASPNETDB, you can install the security schema into an existing database of a different name.

2. Set up a connection string that points explicitly at the SQL Server database i.e. server name, database name, user id and password.

3. Point the Membership provider at this connection string.

I've put a specific example from one of my own web.config files using SQL Server - this is the connection string from mydevelopment environment (so uses localhost for the server name). Can you see that the Membership provider points at the "MainDB" connection string - which is pointing at the SQL Server?

The live web.config settings are very similar, except that instead of localhost I have the name of the server. Hope this helps!

<connectionStrings>

<addname="MainDB"connectionString="Server=localhost;Database=DB_138621;User ID=*****;Password=*****"providerName="System.Data.SqlClient" />

</connectionStrings>

--- snip -----

<membershipdefaultProvider="AspNetSqlMembershipProvider">

<providers>

<clear/>

<addname="AspNetSqlMembershipProvider"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MainDB"minRequiredPasswordLength="5"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"enablePasswordRetrieval="true"enablePasswordReset="false" applicationName="/"/>

</providers>

|||

Thank you Salmon. Using your example and wading through my web.config with fat fingers, I was finally able to make this work.

aspnet_regsql.exe - cannot insert null values

Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errors

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.

------------
Details of failure
------------

SQL Server:
Database: [AddressVerification]
SQL file loaded:
InstallCommon.sql

Commands failed:

CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)

INSERT INTO #aspnet_Permissions
EXEC sp_helprotect

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions

SQL Exception:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??

** I am logged in with full admin privaliges

tia

Mark.

This is because the preinstalled user defined data type sysname by default does not allow nulls--that means column of sysname data type won't accept nulls unless you explicitly set the column to allow null in column definition. So if the sp_helprotect stored procedure returns some rows with nulls in the [column] field (this is an rare issue but it does exist), the insert command will fail. So here I have to say it is a flaw of the aspnet_regsql utility.|||

I wrote a simple script to update the system table systypes to make sysname data type to accept NULLs. However the script is not fully tested and since it modifies system table directly, I don't recommend to use it and you need to consider the potential risk when using it. If some issue is caused by the script, please pass 'NoNulls' as parameter to change the systypes back to default:

CREATE PROCEDURE usp_KillUsers
@.p_DBName SYSNAME = NULL
AS

/* Check Paramaters */
/* Check for a DB name */
IF (@.p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@.p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@.p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables */
DECLARE @.v_spid INT,
@.v_SQL NVARCHAR(255)

/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.v_SQL = 'KILL ' + CONVERT(NVARCHAR, @.v_spid)
-- PRINT @.v_SQL
EXEC (@.v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @.v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users

CREATE PROCEDURE sp_SetSysnameAllowNulls @.Operation varchar(10)='AllowNulls'
AS
BEGIN
DECLARE @.cmd nvarchar(4000)
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
EXEC sp_msforeachdb N'use[?];
IF (DATABASEPROPERTY(db_name(),''IsReadOnly'')=1)
BEGIN
declare @.dbname sysname;
set @.dbname=db_name();
use master
EXEC usp_KillUsers @.dbname;
EXEC sp_dboption @.dbname,''read only'',''false'';
END'

IF (@.Operation = 'AllowNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status-1
WHERE name=''sysname'' AND (status&1)=1'
ELSE
IF (@.Operation = 'NoNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status+1
WHERE name=''sysname'' AND (status&1)=0'
ELSE RAISERROR('Expected @.Opertaion=''AllowNulls'' or ''NoNulls''',16,1)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
END

ASP.NET-Report Viewer-Internet

Hi,
I am planning to use Report viewer web control in my web application and
going to use forms authenthication. What roles need to assign in RS and what
are the other settings need to be done.
Thanks & Rgds,
AruThe roles settings are no different between forms authentication and Windows
authentication. The settings for forms authentication are described here:
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true#ufairs
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Aru" <Aru@.discussions.microsoft.com> wrote in message
news:5B452DEB-E608-4814-B15B-5842E016B9F4@.microsoft.com...
> Hi,
> I am planning to use Report viewer web control in my web application and
> going to use forms authenthication. What roles need to assign in RS and
> what
> are the other settings need to be done.
> Thanks & Rgds,
> Aru