Using Roles... help?

[jpotucek]

I inherited a Database and there are alot of users with individual permissions to a particular database. Someone told me to create a role, give the role permissions to the DB and then add the users

After I create the Role and give the role appropriate access to the database, I would then do the following for existing users:
modify the users to take away their DB_Owner access to the database and then add them to the MyRole.

For NEW users, I would do the following:

Add the new NT authenticated USer to the Server
EXEC sp_grantlogin 'Supreme\UserID'

Make the Database the default Database for this user:
sp_defaultdb 'Supreme\USerID', 'MyDatabase'

Add the user to the role:
sp_addrolemember 'MyRole', 'Supreme\userid'

Does that look like the logical sequence? did I miss anything?

 

[mrdenny]

I wouldn't bother changing the users default database.

Other than that I think that the only thing that you missed was test to make sure everything still works for the existing users.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog