Post reply

How to assign privileges to Windows-authenticated users?

  • September 16, 2008 at 9:22 am

    #127525

    Greetings,

    I have done a little bit of work setting up SQL Server logins, associated users, and their permissions. I am confused as to how to "get ahold of" Windows logins and associate them with users and set permissions for them. When I look under the user database I want to assign them to, none of the Windows-authenticated logins are there, and if I try to create "new user", SQL Server gives me an error sayings "Windows NT user or group "\\domain\user" (specifics omitted) not found.

    Clearly I'm not understanding something correctly.

    Thanks for your assistance,

    Randy

  • September 16, 2008 at 9:48 am

    #872499

    problem is... I'm not sure I understand what you mean, could you please clarify?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 16, 2008 at 9:59 am

    #872514

    I guess, ultimately, what I want to do is set up various Application Roles and assign groups of Windows-authenticated users to these roles. I don't know how to do this from within Management Studio. That is, I know how to set up Application Roles. I don't know how to associate the Windows logins with those roles.

    Thanks,

    Randy

  • September 16, 2008 at 10:05 am

    #872521

    Are you trying to set up application roles, or just roles? If you create a new role in a database, you can then go to the logins (at the server level, not the users at the database level) edit the security of a windows login and select the role within the database.

    This will give them a user association in the database if the login does not already have one.

  • September 16, 2008 at 10:15 am

    #872526

    Randy -- Application roles are not assigned to users -- for a rather lengthy, but valuable discussion of how to use Application Roles and how users access these roles please read this forum:

    http://www.sqlservercentral.com/Forums/Topic533262-146-1.aspx

    If this does not help then come on back and post additional questions.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 16, 2008 at 10:27 am

    #872530

    What I was thinking, was to set up application roles, each permitting access to a different set of stored procedures and/or views in a particular database. Simplified examples would be: User A may need write access to R & D data, but view access to production data and order data. User B may need view access to R & D data, write access to production data and view access to order data. User C may be prohibited from any access to R & D data, need view access to production data, and write access to order data.

    Maybe this can be done just as easily by setting up new database roles, I don't know. I'm fairly new to SQL Server and haven't done too much with roles yet.

    Either way, once these application/database roles are set up, I want to associate Windows logins with the various roles. Is this do-able from within either SQL Server or Management Studio? It appears, from your reply, that this must be done at the Windows level? If so, then I'll need to interact with our network adminstrator, as I'm not given access to change Windows settings, only things within SQL Server, databases and applications.

    Thanks,

    Randy

  • September 16, 2008 at 10:38 am

    #872533

    Randy,

    I'm not sure your original question has been answered. Before you can add a database user for a Windows login, you have to add the login to the SQL Server instance.

    In SQL Server Management Studio Object Explorer, expand Security, right-click Logins, and and select "New Login...". When the Login - New screen pops up, click "Windows authentication", click the "Search" button, and find the Windows login you want. Select the "User Mapping" page and check the database to which you want to add a user. When you click "OK", you'll have created the login and the user.

    Greg

  • September 16, 2008 at 10:39 am

    #872534

    REad the article that bitbucket posted.

    From your short description, you do not want application roles. You just want database roles - these can be associated with logins. Application roles are different.

  • September 16, 2008 at 10:48 am

    #872543

    Thanks very much to those who responded.

    Bitbucket, from the link you suggested, it started to become apparent that it was database roles, not application roles, that I was needing. SSCrazy, your comments provided the confirmation and filled in the missing pieces I needed, especially regarding how to incorporate the Windows users.

    Thanks again & best wishes,

    Randy

  • September 16, 2008 at 12:16 pm

    #872610

    randy.witt (9/16/2008)

    What I was thinking, was to set up application roles, each permitting access to a different set of stored procedures and/or views in a particular database. Simplified examples would be: User A may need write access to R & D data, but view access to production data and order data. User B may need view access to R & D data, write access to production data and view access to order data. User C may be prohibited from any access to R & D data, need view access to production data, and write access to order data.

    Maybe this can be done just as easily by setting up new database roles, I don't know. I'm fairly new to SQL Server and haven't done too much with roles yet.

    Either way, once these application/database roles are set up, I want to associate Windows logins with the various roles. Is this do-able from within either SQL Server or Management Studio? It appears, from your reply, that this must be done at the Windows level? If so, then I'll need to interact with our network adminstrator, as I'm not given access to change Windows settings, only things within SQL Server, databases and applications.

    Thanks,

    Randy

    Setup Global groups in Active Directory, GG_R&D_Write, GG_R&D_Read, GG_Prod_Write .....

    then put the individual Windows Users into those global groups, and give the global groups SQL permissions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply