Post reply

Access Granted for : CREATE USER [Domain1\UserName] FOR LOGIN [Domain1\UserName]

  • March 14, 2016 at 12:07 pm

    #399857

    Hi All

    I have a question about the User Access for a Database. My Scenario is as below

    I have a database refresh from Production(Server 1) to a different server(Server 2) on nightly basis and the users added to Server 2 loose access after every refresh. For which I am using below script to create the users on the refreshed database (Server 2).

    CREATE USER [Domain1\UserName] FOR LOGIN [Domain1\UserName]

    -- the above script is derived from below query

    SELECT 'IF EXISTS(SELECT 1 FROM sys.server_principals sr_pri WHERE name ='''+ SUSER_SNAME(sid) +''') BEGIN CREATE USER [' + SUSER_SNAME(sid) + '] FOR LOGIN [' + SUSER_SNAME(sid) + '] END ' FROM sys.database_principals WHERE [type] IN ('G', 'U')

    GO

    My question is about the access. I want to know what level of access does the user get and from where. Is it always the data_reader access?. I checked for couple of them and it is read access by default. If I want to retain the write access given to the user before the refresh how can I make sure the users gets it.

    Thanks

    Anjali.

  • March 14, 2016 at 1:07 pm

    #1865038

    creating a user does not give then any access to the objects within the database; they are added to thePUBLIC role in the database, which typically doesn't give them anything.

    if they can access anything, it's because they are in a group that was granted access, and they inherited that groups permissions.

    drill into the user via the GUI, and see what roles the user belongs to.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2016 at 1:33 pm

    #1865046

    If you get your users and logins setup so they're using the same SIDs on both production and dev, I don't think you'll have this problem.

    ...I'm thinking the restore is creating orphaned users. So rather than re-creating the users every restore, just fix the orphaned users one time and that should be it.

  • March 14, 2016 at 1:59 pm

    #1865055

    Thank you Lowell for the information.

    And also to let you know, for the first time when the Database was refreshed I manually added couple of users and gave them read access so does this mean after refresh when I recreate the user it will still hold the acess that I gave them before? Because when I see from GUI the role for the database it says data_reader.

  • March 14, 2016 at 2:05 pm

    #1865056

    if you are restoring from another server, any changes you made will be lost, so you would need to either:

    1. grant that read access in the original server/database before backup and restore:

    2. Script the roles/users/permissions so that you can rerun the script after the restore.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 14, 2016 at 2:22 pm

    #1865059

    Thank you Jon. I will see how I can fix the orphan records.

    If fixing onetime will take care for the rest of the times then may be I will try doing this.

  • March 14, 2016 at 2:27 pm

    #1865060

    Before using the Create User script I was doing the below and it didn't help so I was using the create user script.

    -- Create Login

    IF EXISTS(SELECT 1 FROM sys.server_principals sr_pri WHERE name ='domain\username') BEGIN DROP LOGIN [domain\username] CREATE LOGIN [domain\username] FROM WINDOWS END ELSE BEGIN CREATE LOGIN [domain\username] FROM WINDOWS END

    -- Grant Permissions

    EXEC sp_addrolemember [db_owner], [domain\username]

    EXEC sp_addrolemember [db_accessadmin], [domain\username]

Viewing 7 posts - 1 through 6 (of 6 total)

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