Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    10

    Question Unanswered: Moving DBs from SQL Server logins to Windows logins

    I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.

    The app executable is tied to a Windows service.
    When we install the app, we run a process that builds 2 dbs to include:
    Tables, indexes, stored procedures, views and user accounts.
    SQL Server is set up for mixed mode authentication.

    Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
    We have a client that wants to remove our standard logins so that they can run on only a Windows login.
    I know I should be able to tie the db users to a Windows login.
    And I can do the same for the service.

    But I am at a loss as to how to get this done.
    How do you associate db users with a Windows login?
    When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)

    Hope this all makes sense.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I think that I would approach it this way:

    1. Create a database role
    Code:
    sp_addrole [ROLE]
    2. Grant all the permissions that the db user has to the role. You can determine the user permissions by executing:
    Code:
    sp_helprotect null, '[username]'
    To grant permissions for tables and or views:
    Code:
    GRANT [SELECT, INSERT, UPDATE, DELETE] ON [TABLE] to [ROLE]
    To grant permissions for stored procedures and or functions:
    Code:
    GRANT EXEC on [OBJECT] to [ROLE]
    Then, grant the Windows group login access to the database server:
    Code:
    sp_grantlogin [DOMAIN\User]
    Then, grant the Windows group access to the particular database:
    Code:
    use [DatabaseName]
    GO
    
    sp_grantdbaccess [DOMAIN\User]
    GO
    Finally, add the windows group to the database role:
    Code:
    sp_addrolemember [ROLE], [DOMAIN\User]
    GO
    Have you hugged your backup today?

  3. #3
    Join Date
    Apr 2007
    Posts
    10

    Thanks

    Thanks hmscott!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •