I am wholly showing my age with the title of this blog post. The title of this post is quote that may or may not mean anything to you. It will, sadly, mean even less to you if you were not in the UK during the 1980's. Apologies for that but on with the blog regardless.
I have recently moved a set of databases from one pair of SQL Servers to another. The main databases that were moved are also restored to a 2nd SQL Instance in order to provide a database that can be used exclusively for OLAP reporting. For this we use Log Shipping and the OLAP database is therefore in a Read-Only state. I had to carry out the following process in order to re-establish the correct security on this OLAP server/database combination.
When you create a Server Principal (previously/alternatively known as a SQL Server Login ) it gets given a unique SID (Security ID) value by the server. This is a unique value and maps directly to the SID of a Database Principal (previously/alternatively known as a Database User) so that access to databases is achieved and maintained.
Lets have a look at Server and Database Principal details:
USE MASTER
GO
-- server principles
SELECT *
FROM [sys].[server_principals] AS sp
WHERE [sp].[principal_id] > 267
/*I have filtered out principal_id values that are irrelevant to this explanation.
You can remove or alter this number to see other information. be careful that you
don't alter values for any rows other than those that you create for testing other
wise you may affect your ability to connect to the server*/
/*plenty of information here by using 'SELECT *' but today we are only really
interested in the values of the name, principal_id and sid columns*/
-- database principals
SELECT *
FROM [sys].[database_principals] AS dp
/*again plenty of information here but today we are still only interested
in the name, principal_id and sid values*/
as you might expect, a database User record is specific to each database so running the same TSQL in different databases will return different results
USE [AdventureWorks]
GO
-- database principles are specific to the database to which you are currently
-- connected
SELECT [name] ,
[principal_id] ,
[sid]
FROM [sys].[database_principals] AS dp
Let's take the knowledge of where this information is and put it to use.
-- clear out test details
-- -*- WARNING -*-
-- -*- if you are running this on your server -*-
-- -*- make sure you aren't about to drop users that you need. -*-
USE [AdventureWorks]
GO
IF EXISTS ( SELECT name
FROM [sys].[database_principals] AS dp
WHERE [name] = 'sqluser01' )
DROP USER [sqluser01]
USE [Master]
GO
IF EXISTS ( SELECT NAME
FROM [sys].[server_principals] AS sp
WHERE [name] = 'sqluser01' )
DROP LOGIN [sqluser01]
-- create login
-- Normally we create a Server Login and then create a Database User so
-- lets do that...
USE [Master]
GO
CREATE LOGIN [SQLUser01] WITH PASSWORD=N'test', DEFAULT_DATABASE=[tempdb],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- create user
USE [AdventureWorks]
GO
CREATE USER [SQLUser01] FOR LOGIN [SQLUser01]
GO
-- and check the Login and User are there as expected
SELECT [name] ,
[principal_id] ,
[sid]
FROM [sys].[server_principals] AS dp
WHERE [principal_id] > 267
SELECT [name] ,
[principal_id] ,
[sid]
FROM [sys].[database_principals] AS dp
WHERE [name] = 'sqluser01'
If we compare those details side by side we see a crucial similarity between the User and the Login
/*
Notice how the SID in both results is the same. SQL Server has used the
one created in Server_Principles for the Database_Principal too
*/
SELECT [sp].[name] AS [server principal name] ,
[sp].[sid] AS [server principal sid] ,
[dp].[name] AS [database principal name] ,
[dp].[sid] AS [database principal sid]
FROM [sys].[server_principals] AS sp
LEFT JOIN [sys].[database_principals] AS dp ON [sp].[name] = [dp].[name]
WHERE [sp].[principal_id] > 267
GO
This means that regardless of what the server and database principals are called, the SIDs are used to relate the two together and therefore facilitate access to data. A Server Principal alone cannot access data without a Database Principal that shares the same SID; If there isn't a corresponding SID in both tables then there is no access to data.
This is an important concept to keep in mind when you re restoring databases from one server to another. The database you are restoring will have the Database Principal SIDs from the other server, and we know what that means. No data access.
/*
Normally, when you restore a database to a different SQL Server the Database
Principal has a different SID. In order to effect access for the User with
out going through the DROP USER, DROP LOGIN, CREATE LOGIN, CREATE USER process
it is easier to use this code to update the Database Principal with the SID of the Server Principal.
This isn't relevant to this case so the code below is purely for information.
-- re-map the user and the login
ALTER USER [SQLUser01] WITH LOGIN = [SQLUser01]
GO
*/
-- But what if the database is read-only - such as the OLAP database I described
-- at the start? one that is being restored by log shipping.
-- the database_principal has been created by a different SQL Server so the SID
-- certainly wont match and we cant alter the SID for the Database Principal as
-- the database is read-only.
USE [Master]
GO
-- lets drop our Server Principal and re-create it to simulate this scenario
IF EXISTS (SELECT NAME
FROM [sys].[server_principals] AS sp
WHERE [name]='sqluser01')
DROP LOGIN [sqluser01]
-- recreate Server login
CREATE LOGIN [SQLUser01] WITH PASSWORD = N'test', DEFAULT_DATABASE=[tempdb],
CHECK_EXPIRATION=OFF, CHECK_POLICY = OFF
So now we have the Server Principal and the Database Principal with different SIDs because the CREATE LOGIN will have generated a new SID.
/*
Show how the SID differs between the Server Principal and the Database Principal.
*/
USE [AdventureWorks]
GO
SELECT [sp].[name] AS [server principle name] ,
[sp].[sid] AS [server sid] ,
[dp].[name] AS [database principle name] ,
[dp].[sid] AS [database principle sid]
FROM [sys].[server_principals] AS sp
LEFT JOIN [sys].[database_principals] AS dp ON [sp].[name] = [dp].[name]
WHERE [sp].[principal_id] > 267
GO
/*
The only option is to control the SID issued by SQL Server when you create
the Server Principal so sadly this method does involve dropping and recreating it and that might affect access to other databases that the Server Principal has/had access to
You need to recover the Principal SID from the Database Principals and provide
that value for the SID parameter used in the CREATE LOGIN statement
*/
-- create login
USE [master]
GO
IF EXISTS (SELECT NAME
FROM [sys].[server_principals] AS sp
WHERE [name]='sqluser01')
DROP LOGIN [sqluser01]
CREATE LOGIN [SQLUser01] WITH PASSWORD=N'test', DEFAULT_DATABASE=[tempdb],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, SID=
/*substitute your SID value here*/0x485DA3E1791D104DBCF728F49831D300
GO
Now we should see that the SIDs are the same for the Server Principal and the Database Principal and therefore the data will be accessible as desired.
/*
Now we will see that the SIDs for Server and Database Principals for SQLUser01
will be the same and that user will be able to access the read-only database
on the OLAP server without problems
*/
USE [AdventureWorks]
GO
SELECT [sp].[name] AS [server principle name],
[sp].[sid] AS [server sid],
[dp].[name] AS [database principle name],
[dp].[sid] AS [database principle sid]
FROM [sys].[server_principals] AS sp
LEFT JOIN [sys].[database_principals] AS dp ON [sp].[name] = [dp].[name]
WHERE [sp].[principal_id]>267
GO
There is just the need to ensure that the Server Principal has the same permissions as on the instance that the database came from to ensure there is no security risk inherited and you are all done.