locked
SQL Server 2005 - Logins and permissions RRS feed

  • Question

  • I have a situation like multiple logins (windows authentication), and multiple databases.

    Now, I want to give some specific permissions to a group of logins to access the db.

    permissions like:

    1. some logins will have -- only datareader permissions on some db's ( not all the databases )
    2. some logins will have -- only datawriter permissions on some db's ( not all the databases )
    3. some logins will have -- both datareader and writer permissions on some db's  ( not all the databases )
    4. some logins will have -- able to change the syntax of the db's ( not all the databases )

    I think, we can do this for individual logins. But, if we have huge no of logins it will be very difficult, so can someone please tell me how can we do this at once.

    could you please answer with step-by-step scripts??

    Thanks,
    Sr
    Thursday, August 20, 2009 3:41 PM

Answers

  • You'll need to create windows groups at the domain/workgroup/server level in the Active Directory/Server. These are not part of sql server but can be added as logins.

    So you will have a Windows Group called DOMAIN\MySQLUsersA which will contain all your windows logins that you want to have the same set of permissions. You would then add the windows group as a login and assign permission to this login. All users within that group would have the permisions set on the group.

    HTH!
    every day is a school day
    Friday, August 21, 2009 1:13 PM

All replies

  • Are you able to group your Windows logins into Windows groups and then assign permissions against the groups?
    every day is a school day
    Thursday, August 20, 2009 3:52 PM
  • How can i create Windows groups? is that same as database roles? If yes, its necessary to create the groups for each and every database right?

    Could you please let me know how can i create a group for windows logins at server level ( not db level ) and assign the permissions.


    Thanks a lot..

    Sr
    Friday, August 21, 2009 11:21 AM
  • You'll need to create windows groups at the domain/workgroup/server level in the Active Directory/Server. These are not part of sql server but can be added as logins.

    So you will have a Windows Group called DOMAIN\MySQLUsersA which will contain all your windows logins that you want to have the same set of permissions. You would then add the windows group as a login and assign permission to this login. All users within that group would have the permisions set on the group.

    HTH!
    every day is a school day
    Friday, August 21, 2009 1:13 PM
  • Thanks a lot. I am done with it.


    Have created the windows groups and gave permissions to those groups.


    Thanks for your help..

    - Sr
    Friday, August 21, 2009 3:25 PM
  • Hi,

    I have faced one more problem doing the above process.

    i.e, After creating the windows group, i have logged in as one user of a particular group, and i created table in the database using query editor. Its creating the table schema as user name not with the dbo .

    Is there any way to assign the default_schema as dbo at the time of creating Windows groups/users.

    Please help..

    Thanks,
    Sr
    Monday, August 24, 2009 2:38 PM
  •   You can use the DEFAULT_SCHEMA option for existing users, but unfortunately this option is not available for Windows groups. A workaround would be to explicitly use 2 part names.


     
    This is a known limitation on Windows groups, and there are several posts about this topic, including a feedback request on this topic: (http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/52cad95e-1382-4a7a-ad7c-56d9f99a6979/). I would strongly recommend reading this particular thread and give us feedback on this subject. I would also strongly recommend going to SQL Server Connect (
    http://connect.microsoft.com/sql) and request this new feature (or vote for existing feedback requesting it).

     

    As a best practice, I would also recommend not using the dbo schema to create all your objects. By  creating all objects under dbo, you may be have conflicts for object names if the database hosts more than one application, and even more, you may be granting implicit access to some data via

    ownership chaining.

      I would recommend reading User-Schema separation (http://msdn.microsoft.com/en-us/library/ms190387.aspx) in BOL.

      I hope this information helps.
     -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 24, 2009 5:46 PM
  • Thanks for your information..

    Is there any other way to give some particular permissions to group of logins.

    We have like Databases(100+) and logins (30+), we need to restrict the user access on database as required.

    So, please let me know the better way of doing this.


    Thanks a lot

    Sr
    Wednesday, August 26, 2009 3:31 PM