January 29, 2008 at 8:03 am
I know that I can do this to add a new NT User to the Database and grant them access to a specific Database
--Add NT UserID to SQL Server
Use ChangeMgmt
EXEC sp_grantlogin 'Domain\UserID'
GO
--Grant Access to ChangeMgmt Database
Use ChangeMgmt
EXEC sp_grantdbaccess 'Domain\UserID, 'Domain\UserID'
GO
but I can't figure out how to take it one step further and grant the user specific permissions in the Database??!!
January 29, 2008 at 8:36 am
Create a role, add the user to the role.
Grant permissions to the role with GRANT SELECT on MyTable to MyRole. Use appropriate permissions for your role.
January 29, 2008 at 9:21 am
Thank you so much Steve!! I'm in a new position and kind of a newbie too.. The DBA Before me granted permissions to a particular Database individually. If I look in logins under the Security tab in Ent Manager, there are like 40 users - all with the same permissions to ONE Database. I'm thinking that I can create the role and then drop all of the individual permissions.. is that right?
January 31, 2008 at 6:49 am
Still kind of confused on this.
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply