ASP.Net Membership SQL Roles access
We have recently moved over to SQL Server 2005 and as part of this transfer I decided to aggregate two separate ASP.Net Membership databases that were created purely out of error.
For those of you who don’t already know, you can happily run more than application’s security from a single membership database as long as you correctly configure the web.config’s security settings –for more information on doing that see my post on having dual records in the ASP.Net authentication table (see: Dual Records In The ASPNet Authentication Table). The important attribute/value set to configure if you are planning on running more than one application from the same roles database is “applicationName”. If you do not set “applicationName” you will find that users can log in across all your applications, roles/access levels will get mixed up and a whole bunch of other hullabaloo!
Luckily for me, the only records stored in one of the membership databases were two users, both of which I knew the password to so I decided I would simply update the web.config with the new database connection string and add them manually.
The next thing I wanted to sort however was the specific SQL Login’s access to the membership database, previously I simply added the user to all the various aspnet_ roles that were in the database which worked fine. As I’m looking to use this database for other applications in the future and I don’t like sharing usernames/passwords across applications, adding the roles each time would become a real PITA so I decided to add a new role with all the access required for the database so I could simply add the user to this new role. I called the role IIS_User.
A number of our applications build on the foundation of the ASP.Net Membership database with application specific values and so I tend to have another table for the application’s users within the applications specific database to store these values. The user has the usual UserId (usually an int) and a uniqueidentifier which allows me to link the two database together. With this in mind, I need additional access to the ASP.Net Membership database –SELECT permission on the tables. I don’t like adding more permissions to a role than needed but I needed a method of doing this quickly –assigning EXECUTE and SELECT permissions to the new role on the various tables/stored procedures. In time I’ll revisit this and work out which are needed by the role and remove the permissions not needed but for now this’ll do :)
The quick and dirty T-SQL
DECLARE @SQL nvarchar(4000),@Owner sysname,
@objName sysname,
@Return int,
@objType nvarchar(5),
@rolename nvarchar(255)
SET @rolename = 'IIS_User'
-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT
USER_NAME(uid) Owner,
[name] StoredProcedure,
xtype
FROM
sysobjects
WHERE
(
xtype = 'U'
OR
xtype = 'P'
)
AND
LEFT([name], 7) = 'aspnet_'
OPEN cursStoredProcedures
-- Get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType
-- Set the return code to 0
SET @Return = 0
-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN
-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
--Determine the object's type (table/stored procedure) -could
--be done using a case too if more objects are added later
IF @objType = 'P'
SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @objName + '] TO ' + @rolename
END
IF @objType = 'U'
SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @objName + '] TO ' + @rolename
END
-- Execute the SQL statement
EXEC @Return = sp_executesql @SQL
-- Get the next row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType
END
-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures
-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
-- Exited fine, commit the permissions
COMMIT TRAN
END
-- Exited with an error, rollback any changes
ROLLBACK TRAN
-- Report the error
SET @SQL = 'Error granting permission to ['
+ @Owner + '].[' + @objName + ']'
RAISERROR(@SQL, 16, 1)
END
Liked this post? Got a suggestion? Leave a comment