Error Granting Database Access In SQL Server Management Studio

Microsoft SQL ServerI’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;

USE [GPNL]
GO

ALTER USER pidev WITH LOGIN = pidev
GO

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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

5 thoughts on “Error Granting Database Access In SQL Server Management Studio

  1. 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 *