Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa.

Then a couple of databases were restored from earlier backups.

Now I'd like to run a few SQL scripts that create new users for these databases, running as sa, but am getting the message that I am not allowed to do this.

share|improve this question
3  
What's the error message that you're getting? –  Thomas Rushton May 19 '13 at 21:22

3 Answers 3

up vote 1 down vote accepted

This means that database users have no matching server logins.

That is, each database sys.database_principals has no match in sys.server_principals

For Windows logins this is easy. This generates your missing CREATE LOGINS

USE MyDB
SELECT
    'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS'
FROM
    sys.database_principals DP
WHERE
    DP.type IN ('G', 'U')
    AND 
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

For SQL Logins, you need to recreate it with the correct SID.

SELECT 
    'CREATE LOGIN ' + QUOTENAME(DP.name) + 
                ' WITH PASSWORD = ''ChangeMe'', SID = ' + 
                CONVERT(varchar(128), sid, 1)
FROM
    sys.database_principals DP
WHERE
    DP.type = 'S' AND DP.principal_id > 4 
    AND 
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

Note that sp_change_users_login is deprecated.

Now, if your SQL Logins already exist, then the names match but the sid values are different. For this you use ALTER USER use the LOGIN option.

Finally, the SQL Login passwords can be recreated if you have a backup of the "old" master database. If you restore this as, say, FixLogins then you can use this

SELECT 
    'CREATE LOGIN ' + QUOTENAME(DP.name) + 
                ' WITH PASSWORD = ' + CONVERT(varchar(256), SL.password_hash, 1) + ' HASHED, SID = ' + 
                CONVERT(varchar(128), DP.sid, 1)

FROM
    sys.database_principals DP
    JOIN
    FixLogins.sys.sql_logins SL ON DP.sid = SL.sid  
WHERE
    DP.type = 'S' AND DP.principal_id > 4 
    AND 
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);
share|improve this answer
    
Wow, thank you so much for this detailed answer! –  DotNetDeveloper May 21 '13 at 10:35

The thing you're trying to fix is what's known as "Orphaned Users" - I blogged about this a while ago, at http://thelonedba.wordpress.com/2013/02/22/restoring-into-a-different-environment-security-problem-orphaned-users/

However, without seeing your exact error message, it's hard to help you with specifics.

share|improve this answer

I Agree With Thomas Rushton. If you have few users Use the Following Process.

  1. Remove the users excepting the [dbo], guest, INFORMATION_SCHEMA, [sys]
  2. and Then Add your users again by right clicking on Users-->New User option

This Processes is doing the same thing what Mr Thomas has suggested but it is manual process.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.