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

Question

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

anju04n6

SSC Enthusiast

Points: 128
More actions
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.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

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.

anju04n6 SSC Enthusiast Points: 128 More actions · Answer 3

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.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

anju04n6 SSC Enthusiast Points: 128 More actions · Answer 5

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.

anju04n6 SSC Enthusiast Points: 128 More actions · Answer 6

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]