Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating new users and granting permissions Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 8:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535

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
Posted Tuesday, January 29, 2008 8:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:29 AM
Points: 32,793, Visits: 14,945
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
Posted Tuesday, January 29, 2008 9:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535
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
Posted Thursday, January 31, 2008 6:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:37 AM
Points: 743, Visits: 1,535

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 »

Add to briefcase

Permissions Expand / Collapse