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.
Best Answer
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
For SQL Logins, you need to recreate it with the correct 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