Error Granting Database Access In SQL Server Management Studio

● Ian Grieve ●  ● 1 Comment  ● 

I’m currently involved with a couple of projects where we have needed to migrate databases from one SQL Server to another and needed to do so more than once.

Prior to the first copy, we used a script to load the users into SQL Server and then restored the databases and had no problems.

Later on, after changing some of the permissions to databases we had need to remigrate some of them over. However, we found that users could not access some databases and when we tried to add them we got the following error;

Msg 15023, Level 16, State 1, Line 1
User, group, or role 'pidev' already exists in the current database.

It was looking very like we would need to go through each database and remove access and then regrant it all; a labourious process. A quick Google found me a page from Julian Kuiters which introduced me to the ALTER USER command. The following code will run against the selected database and tie together the SQL Server user with the user in the database without any other work;



The first highlighted element needs to be changed to the database you want to update the user on and then the following two entries are the usernames which need to be linked (these should be set to the same value).

Update: if your usernames contain a space (e.g. I Grieve) then wrap them in square brackets: e.g. [I Grieve]

What should we write about next?

Please leave this field empty.

Your Name (required) -
Your Email (required) -
● Categories: Microsoft, SQL Server, SQL Server 2008 ● Tags: , , , ,  ● Permalink ● Shortlink ●

1 Response

  1. Ian Grieve Ian Grieve says:

    If you get the following error:

    Msg 15401, Level 16, State 2, Line 2
    Windows NT user or group 'I Grieve' not found. Check the name again.

    This means that the user you’re working with does not exist as a SQL Login, but only as a user against the database. Create the SQL Login and re-run the script.

Leave a Reply

Your email address will not be published. Required fields are marked *