creating new users and granting permissions

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

  • 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.

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

  • 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