creating new users and granting permissions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » creating new users and granting permissions

creating new users and granting permissions

Rate Topic

Display Mode

Topic Options

Author

Message

Jpotucek

Posted Tuesday, January 29, 2008 8:03 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:56 AM
Points: 718, Visits: 1,470

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??!!

Post #448868

Steve Jones - SSC Editor

Posted Tuesday, January 29, 2008 8:36 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425, Visits: 13,738

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #448904

Jpotucek

Posted Tuesday, January 29, 2008 9:21 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:56 AM
Points: 718, Visits: 1,470

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?

Post #448946

Jpotucek

Posted Thursday, January 31, 2008 6:49 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:56 AM
Points: 718, Visits: 1,470

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?

Post #449918

« Prev Topic | Next Topic »

Permissions