Re-generating SQL Server Logins
Microsoft SQL Server stores all login permissions in security catalog system tables. By querying the system tables, database administrators can produce a comprehensive report for login permission including original password, the server and database roles assigned, down to granular level object permission.
What’s the deal with logins and how it affects a database system
It is a common task for a DBA to make sure user login permissions are properly assigned. Depending on the purpose of the logins, we grant an appropriate level of permission to ensure that the login can perform the tasks it needs to do and nothing more.
We replicate back-ends into multiple environments quite often. Each of them serves a different purpose, like development, testing, user acceptance testing, pilot and production environments. The very same login may have different levels of permission in each environment. Permissions are tightened up gradually from development to QA, then from QA to UAT, into Pilot and eventually completely secured in Production.
Other than maintaining multiple environments with completely different privilege settings, DBAs also often face tasks like Database/Server migration, breaking down one server into multiple environments but requiring exactly the same permission settings in all servers. The last thing a DBA wants to face when users from different systems calling in and complaining that their logins don't perform the tasks that used to work before the segregation.
Last but certainly not the least is compliance auditing. More and more companies have gone under extensive scrutinization on the level of permission a login possesses in production systems. Auditors usually wanted to know what logins have elevated privileges and the justification for them. So it comes in handy if a DBA can quickly find the permission setup for all of the logins on monitored servers and take a step ahead to do some clean-up before reviewing login permissions with auditors.
In some cases, logins are required to have privileges downgraded or removed from the production systems. Reverse engineering the login comes in handy to analyze how login permission was configured for the server level, database level and object level before proceeding to any login permissions changes or login removal. With the presence of a reversing script, we can easily revert to its original setting in case the login changes impacted normal application or user operations.
MS SQL Server has stored all login permissions on security catalog system tables. By querying the system tables, we can re-generate a SQL statement to cover from the very beginning on how a login is generated along with the original password, getting server and database roles assigned, and down to granular level object permission. We can also produce a comprehensive report for login permission by combining information from system metadata.
Login properties
We will start by creating a login along with the properties. There are four types of logins within MS SQL Server: SQL Server login, Windows logins, certificate-mapped logins, and asymmetric key-mapped logins. Here, we will focus on SQL Server login and Windows login since they are the most commonly used. Password generation will be covered in the script but not DROP LOGIN statement. This is to avoid any accidental login drop on the production system. We will utilize sp_hexadecimal from Microsoft support and extract some code from sp_help_revlogin to generate a hashed password and sid.
Starting with SQL 2005, you can enable password policy, password expiration and force user to change password at next login for SQL Server login, much like the password policy defined for our Windows logins. Password check policy is on by default, whereas password expiration policy is off by default. These two policies are only enforced on Windows 2003 or later. If you want user to change the password at the next login, then both policies will automatically set as ON. Since these policies are only for SQL Server logins, you will notice that for Windows logins, none of these properties will be included in the generated statement.
Pre-requisite utility stored procedure
Before we look into how to generate a create statement for both SQL Server login and Windows login for MS SQL Server. You need to create stored the procedure sp_hexadecimal as a pre-requisite. Stored procedure sp_hexadecimal works on SQL 2005, 2008 and 2008 R2.
--------------------------------------- -- Stored Procedure sp_hexadecimal --------------------------------------- USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO
Reverse engineering Login creation
Now we can proceed with generating a create login statement for both SQL Server login and Windows login. Replace 'YourLoginName' with the login you would like to generate. This script works on SQL 2005, SQL 2008 and SQL 2008 R2. Assuming you have adequate permission on the instance to run all subsequent scripts, execute the following code.
---------------------------------------------- --Login Pre-requisites ---------------------------------------------- USE master go SET NOCOUNT ON DECLARE @login_name varchar(100) SET @login_name = 'YourLoginName' IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S')) BEGIN PRINT 'Please input valid login name' RETURN END DECLARE @login_sid varbinary(85) SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name DECLARE @maxid int IF OBJECT_ID('tempdb..#db_users') is not null DROP TABLE #db_users SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases SELECT @maxid = @@ROWCOUNT --------------------------------------------- --Retrieve hashed password and hashed sid --------------------------------------------- IF EXISTS (SELECT * FROM sys.server_principals WHERE type = 'S' and name = @login_name ) BEGIN DECLARE @PWD_varbinary varbinary (256) SET @PWD_varbinary = CAST( LOGINPROPERTY( @login_name, 'PasswordHash' ) AS varbinary (256) ) DECLARE @SID_string varchar (514) DECLARE @PWD_string varchar (514) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @login_sid, @SID_string OUT END --select @SID_string --select @PWD_string ---------------------------------------------- --Login Properties ---------------------------------------------- PRINT '----------------------------------------------' PRINT '--SET Login Properties' PRINT '----------------------------------------------' DECLARE @login_sqlcmd varchar(1000) SET @login_sqlcmd = '' SELECT @login_sqlcmd = '-- LOGIN ['+@login_name+'] IS '+case is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END FROM sys.server_principals WHERE name = @login_name IF EXISTS (SELECT * FROM sys.sql_logins WHERE name = @login_name) BEGIN SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE LOGIN '+ QUOTENAME(@login_name)+' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = ['+default_database_name+'], DEFAULT_LANGUAGE = ['+default_language_name+']' FROM sys.server_principals WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd + ', CHECK_POLICY' + CASE is_policy_checked WHEN 0 THEN '=OFF' ELSE '=ON' END FROM sys.sql_logins WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd + ', CHECK_EXPIRATION' + CASE is_expiration_checked WHEN 0 THEN '=OFF' ELSE '=ON' END FROM sys.sql_logins WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'ALTER LOGIN ['+@login_name+'] WITH DEFAULT_DATABASE = ['+default_database_name+'], DEFAULT_LANGUAGE = ['+default_language_name+']' FROM sys.server_principals WHERE name = @login_name END ELSE BEGIN SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE LOGIN ' + QUOTENAME( @login_name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + default_database_name + ']' FROM sys.server_principals WHERE name = @login_name END PRINT @login_sqlcmd
By executing the above script, you will see a result similar to the snapshot below in the SSMS result pane. [Refer Fig 1.0 and Fig 1.1]
Fig 1.0
For Windows login, the result looks like this. There will be no hashed password, sid and password policy defined for Windows login.
Fig 1.1
Server level role permission for a login
Now we have the script used to recover login create, but a login without any permission doesn’t serve any purpose. So next we will see how to recover sever level permission for a login by running the statement below on SSMS.
---------------------------------------------- --Login Pre-requisites ---------------------------------------------- USE master go SET NOCOUNT ON DECLARE @login_name varchar(100) SET @login_name = 'YourLoginName' IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S')) BEGIN PRINT 'Please input valid login name' RETURN END DECLARE @login_sid varbinary(85) SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name DECLARE @maxid int IF OBJECT_ID('tempdb..#db_users') is not null DROP TABLE #db_users SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases SELECT @maxid = @@ROWCOUNT ---------------------------------------------- --Grant Server Role to login ---------------------------------------------- PRINT '' PRINT '----------------------------------------------' PRINT '--Grant Server Role to login ' PRINT '----------------------------------------------' IF OBJECT_ID('tempdb..#srvrole') IS NOT NULL DROP TABLE #srvrole CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85)) INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember DECLARE @login_srvrole varchar(1000) SET @login_srvrole = '' IF EXISTS (SELECT 1 FROM #srvrole WHERE[MemberName] = @login_name) BEGIN SELECT @login_srvrole = @login_srvrole + 'EXEC sp_addsrvrolemember '''+MemberName+''', '''+ServerRole+'''' FROM #srvrole WHERE [MemberName] = @login_name PRINT @login_srvrole END ELSE BEGIN PRINT '--Login ['+@login_name+'] is not a member of any server level role' END
You can see a result similar to the snapshot below on the result pane. [Refer Fig 1.2]
Fig 1.2
If the login you inquired doesn’t have any server level permission, then the result pane will display as below. [Refer Fig 1.3]
Fig 1.3
Database level role permission for a login
Now, a login may be a member of a specific database role. It’s important we don’t miss out restoring database role permission setting for a login.
---------------------------------------------- --Login Pre-requisites ---------------------------------------------- USE master go SET NOCOUNT ON DECLARE @login_name varchar(100) SET @login_name = 'YourLoginName' IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S')) BEGIN PRINT 'Please input valid login name' RETURN END DECLARE @login_sid varbinary(85) SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name DECLARE @maxid int IF OBJECT_ID('tempdb..#db_users') is not null DROP TABLE #db_users SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases SELECT @maxid = @@ROWCOUNT --------------------------------------------------- --Find out list of db that the login has access to --------------------------------------------------- IF OBJECT_ID('tempdb..#alldb_users') is not null DROP TABLE #alldb_users CREATE TABLE #alldb_users( [dbname] [sysname] NOT NULL, [name] [sysname] NOT NULL, [principal_id] [int] NOT NULL, [type] [char](1) NOT NULL, [type_desc] [nvarchar](60) NULL, [default_schema_name] [sysname] NULL, [create_date] [datetime] NOT NULL, [modify_date] [datetime] NOT NULL, [owning_principal_id] [int] NULL, [sid] [varbinary](85) NULL, [is_fixed_role] [bit] NOT NULL ) DECLARE @id int, @sqlcmd varchar(500) SET @id = 1 WHILE @id <=@maxid BEGIN SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id INSERT INTO #alldb_users EXEC (@sqlcmd) SET @id = @id + 1 END DELETE FROM #alldb_users WHERE sid is null DELETE FROM #alldb_users WHERE sid <> @login_sid --SELECT * FROM #alldb_users ---------------------------------------------- --granting database role to login ---------------------------------------------- PRINT '' PRINT '----------------------------------------------' PRINT '--Grant database role to login ' PRINT '----------------------------------------------' IF OBJECT_ID('tempdb..#dbrole') is not null DROP TABLE #dbrole create table #dbrole (dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85), default_schema_name varchar(100), login_name varchar(100), db_principal_id int) DECLARE @dbrole_sqlcmd varchar(max) SET @dbrole_sqlcmd = '' SELECT @dbrole_sqlcmd = @dbrole_sqlcmd + 'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name, b.principal_id as login_name from ['+dbname+'].sys.database_role_members a inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id left join sys.server_principals d on b.sid = d.sid ' from #alldb_users --SELECT @dbrole_sqlcmd --PRINT @dbrole_sqlcmd INSERT INTO #dbrole exec(@dbrole_sqlcmd) --SELECT * FROM #dbrole DELETE FROM #dbrole WHERE sid <> @login_sid ALTER TABLE #dbrole ADD ID INT identity(1,1) DECLARE @counter int, @maxid2 int, @login_dbrole varchar(max) SELECT @maxid2 = MAX(ID) FROM #dbrole SET @counter = 1 --SELECT * FROM #dbrole IF NOT EXISTS (SELECT * FROM #dbrole ) BEGIN PRINT '--Login ['+@login_name+'] is not a member of any database level role' return END WHILE @counter <= @maxid2 BEGIN SELECT @login_dbrole = 'USE ['+dbname+'] IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+dbrole_member+''') BEGIN CREATE USER ['+dbrole_member+'] FOR LOGIN ['+login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+' END ALTER USER ['+dbrole_member+'] WITH LOGIN = ['+login_name+'] EXEC sp_addrolemember '''+dbrole+''','''+dbrole_member+''' ' FROM #dbrole WHERE ID = @counter SELECT @counter = @counter + 1 PRINT @login_dbrole END
If the login does not belong to any database role, then a result similar to the below snapshot will show up in the result pane. [Refer Fig 1.4 and Fig 1.5]
Fig 1.4
Otherwise, here is the sample result that denotes what database role the login is a member of.
Fig 1.5
We need to make sure that a database user is created for the login we are trying to recover on the database. If the login already has a database user defined for it, then we just need to make sure that a user is re-mapped to match the login's SID. sp_change_users_login will be removed in a future version of Microsoft SQL Server. It is good to start avoiding using it and replace it with ALTER USER WITH LOGIN to perform sid remap.
Consolidate above works
By combing all three scripts, you can generate a script that does the following:
1. Create login with original password, original sid, default database setting along with password policy setting
2. Server level role permission
3. Database level role permission
The script below demonstrates how to combine all three scripts into one. It is assumed you have created stored procedure sp_hexadecimal by running previous scripts.
---------------------------------------------- --Login Pre-requisites ---------------------------------------------- USE master go SET NOCOUNT ON DECLARE @login_name varchar(100) SET @login_name = 'YourLoginName' IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S')) BEGIN PRINT 'Please input valid login name' RETURN END DECLARE @login_sid varbinary(85) SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name DECLARE @maxid int IF OBJECT_ID('tempdb..#db_users') is not null DROP TABLE #db_users SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases SELECT @maxid = @@ROWCOUNT --------------------------------------------- --Retrieve hashed password and hashed sid --------------------------------------------- IF EXISTS (SELECT * FROM sys.server_principals WHERE type = 'S' and name = @login_name ) BEGIN DECLARE @PWD_varbinary varbinary (256) SET @PWD_varbinary = CAST( LOGINPROPERTY( @login_name, 'PasswordHash' ) AS varbinary (256) ) DECLARE @SID_string varchar (514) DECLARE @PWD_string varchar (514) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @login_sid, @SID_string OUT END --select @SID_string --select @PWD_string ---------------------------------------------- --Login Properties ---------------------------------------------- PRINT '----------------------------------------------' PRINT '--SET Login Properties' PRINT '----------------------------------------------' DECLARE @login_sqlcmd varchar(1000) SET @login_sqlcmd = '' SELECT @login_sqlcmd = '-- LOGIN ['+@login_name+'] IS '+case is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END FROM sys.server_principals WHERE name = @login_name IF EXISTS (SELECT * FROM sys.sql_logins WHERE name = @login_name) BEGIN SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE LOGIN '+ QUOTENAME(@login_name)+' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = ['+default_database_name+']' FROM sys.server_principals WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd + ', CHECK_POLICY' + CASE is_policy_checked WHEN 0 THEN '=OFF' ELSE '=ON' END FROM sys.sql_logins WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd + ', CHECK_EXPIRATION' + CASE is_expiration_checked WHEN 0 THEN '=OFF' ELSE '=ON' END FROM sys.sql_logins WHERE name = @login_name SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'ALTER LOGIN ['+@login_name+'] WITH DEFAULT_DATABASE = ['+default_database_name+']' FROM sys.server_principals WHERE name = @login_name END ELSE BEGIN SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE LOGIN ' + QUOTENAME( @login_name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + default_database_name + ']' FROM sys.server_principals WHERE name = @login_name END PRINT @login_sqlcmd ---------------------------------------------- --Grant Server Role to login ---------------------------------------------- PRINT '' PRINT '----------------------------------------------' PRINT '--Grant Server Role to login ' PRINT '----------------------------------------------' IF OBJECT_ID('tempdb..#srvrole') IS NOT NULL DROP TABLE #srvrole CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85)) INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember DECLARE @login_srvrole varchar(1000) SET @login_srvrole = '' IF EXISTS (SELECT 1 FROM #srvrole WHERE[MemberName] = @login_name) BEGIN SELECT @login_srvrole = @login_srvrole + 'EXEC sp_addsrvrolemember '''+MemberName+''', '''+ServerRole+''''+CHAR(10) FROM #srvrole WHERE [MemberName] = @login_name PRINT @login_srvrole END ELSE BEGIN PRINT 'Login ['+@login_name+'] is not a member of any server level role' END --------------------------------------------------- --Find out list of db that the login has access to --------------------------------------------------- IF OBJECT_ID('tempdb..#alldb_users') is not null DROP TABLE #alldb_users CREATE TABLE #alldb_users( [dbname] [sysname] NOT NULL, [name] [sysname] NOT NULL, [principal_id] [int] NOT NULL, [type] [char](1) NOT NULL, [type_desc] [nvarchar](60) NULL, [default_schema_name] [sysname] NULL, [create_date] [datetime] NOT NULL, [modify_date] [datetime] NOT NULL, [owning_principal_id] [int] NULL, [sid] [varbinary](85) NULL, [is_fixed_role] [bit] NOT NULL ) DECLARE @id int, @sqlcmd varchar(500) SET @id = 1 WHILE @id <=@maxid BEGIN SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id INSERT INTO #alldb_users EXEC (@sqlcmd) SET @id = @id + 1 END DELETE FROM #alldb_users WHERE sid is null DELETE FROM #alldb_users WHERE sid <> @login_sid --SELECT * FROM #alldb_users ---------------------------------------------- --granting database role to login ---------------------------------------------- PRINT '' PRINT '----------------------------------------------' PRINT '--Grant database role to login ' PRINT '----------------------------------------------' IF OBJECT_ID('tempdb..#dbrole') is not null DROP TABLE #dbrole create table #dbrole (dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85), default_schema_name varchar(100), login_name varchar(100), db_principal_id int) DECLARE @dbrole_sqlcmd varchar(max) SET @dbrole_sqlcmd = '' SELECT @dbrole_sqlcmd = @dbrole_sqlcmd + 'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name, b.principal_id as login_name from ['+dbname+'].sys.database_role_members a inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id left join sys.server_principals d on b.sid = d.sid ' from #alldb_users --SELECT @dbrole_sqlcmd --PRINT @dbrole_sqlcmd INSERT INTO #dbrole exec(@dbrole_sqlcmd) --SELECT * FROM #dbrole DELETE FROM #dbrole WHERE sid <> @login_sid ALTER TABLE #dbrole ADD ID INT identity(1,1) DECLARE @counter int, @maxid2 int, @login_dbrole varchar(max) SELECT @maxid2 = MAX(ID) FROM #dbrole SET @counter = 1 --SELECT * FROM #dbrole IF NOT EXISTS (SELECT * FROM #dbrole ) BEGIN PRINT '--Login ['+@login_name+'] is not a member of any database level role' return END WHILE @counter <= @maxid2 BEGIN SELECT @login_dbrole = 'USE ['+dbname+'] IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+dbrole_member+''') BEGIN CREATE USER ['+dbrole_member+'] FOR LOGIN ['+login_name+']'+isnull (' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+' END ALTER USER ['+dbrole_member+'] WITH LOGIN = ['+login_name+'] EXEC sp_addrolemember '''+dbrole+''','''+dbrole_member+''' ' FROM #dbrole WHERE ID = @counter SELECT @counter = @counter + 1 PRINT @login_dbrole END
In the next article, I will continue to drill down the server and database securable permission for designated login.
Re-generating SQL Server Logins, Part 2
This is the second article on my Re-generate SQL Server login series. In my first column, I focused on server-level role and database-level role assignment for any assigned login. I also mentioned frequent DBA encountered tasks related to ensuring login properties setup and what kind of implications might be created if login permission is not properly defined. In this portion of the series, I’d like to walk through list of securables and how to generate login to include securable granting and denying.
What is a securable?
Simply put, any entity within the database engine that can be secured with permission is called securable. The highest levels of securables are servers and databases, mid-level securables are assemblies, full-text catalogs, message types, etc., and the finest level securables are table, view, function, stored procedure, etc. We can grant, deny and revoke permission on securables.
Server-level permission
Below is the list of server-level permissions. The list on the left also includes the permission defined on the right. For instance, once we grant a login control server permission, the login will also have privilege to do bulk operation, create and alter any database, create unsafe assembly and shutdown the SQL instance. We need to be extra careful when granting anyone server-level permission. One thing worth mentioning is that by default all users have permission to "VIEW ANY DATABASE." Also, to grant server-level permission, we need to be in the master database to issue the server scope granting command and we either need to be a sysadmin or possess the server-level permission and higher with GRANT option to give others permission on server level. The server-level permission information is stored on sys.server_permissions.
Server Permission | Finer Server Permission |
CONTROL SERVER | ADMINISTER BULK OPERATIONS |
CONTROL SERVER | ALTER ANY CONNECTION |
CONTROL SERVER | ALTER ANY CREDENTIAL |
CONTROL SERVER | ALTER ANY DATABASE |
CONTROL SERVER | ALTER ANY ENDPOINT |
CONTROL SERVER | ALTER ANY EVENT NOTIFICATION |
CONTROL SERVER | ALTER ANY LINKED SERVER |
CONTROL SERVER | ALTER ANY LOGIN |
CONTROL SERVER | ALTER ANY SERVER AUDIT |
CONTROL SERVER | ALTER RESOURCES |
CONTROL SERVER | |
CONTROL SERVER | ALTER SETTINGS |
CONTROL SERVER | ALTER TRACE |
CONTROL SERVER | AUTHENTICATE SERVER |
CONTROL SERVER | CONNECT SQL |
CONTROL SERVER | CONTROL SERVER |
CONTROL SERVER | EXTERNAL ACCESS ASSEMBLY |
CONTROL SERVER | SHUTDOWN |
CONTROL SERVER | UNSAFE ASSEMBLY |
CONTROL SERVER | VIEW ANY DEFINITION |
ALTER ANY EVENT NOTIFICATION | CREATE DDL EVENT NOTIFICATION |
ALTER ANY EVENT NOTIFICATION | CREATE TRACE EVENT NOTIFICATION |
ALTER ANY ENDPOINT | CREATE ENDPOINT |
ALTER ANY DATABASE | CREATE ANY DATABASE |
VIEW ANY DEFINITION | VIEW ANY DATABASE |
Permission on database-level securable
Unlike server-level permission that you can only define when connecting to the master database, each database has its list of pre-defined class of securable that you can grant permission on database principal (database user). Here is the list of securable class on which permission exists:
- Database
- Object or column
- Schema
- Database principal
- Assembly
- Type
- XML schema collection
- Message type (Service Broker securable)
- Service contract (Service Broker securable)
- Service (Service Broker securable)
- Remote Service Binding (Service Broker securable)
- Route (Service Broker securable)
- Full-Text Catalog
- Symmetric Key
- Certificate
- Asymmetric Key
Database-level Permission information is stored on sys.database_permissions. Unless sys.server_permissions, sys.database_permissions exists for every database.
Grant, revoke and deny
The definition for GRANT, REVOKE and DENY can be confusing sometimes. Simply put, revoke is used to remove the permission that has been GRANTED or DENIED on the same securable.
But what if we grant permission to a lower scope securable and then issue DENY or REVOKE on a higher scope securable, what will happen to the lower scope securable? No surprise, there is a difference.
When Denying on a higher-level securable, the principal will be denied on the lower-level securable.
When Revoking on a higher-level securable, the principal will keep his granted permission on the lower scope securable.
A principal can grant permission to another principal if his permission is granted with grant option.
Generate script for server and database securable class
We have briefly gone over the available list of server-level and database-level securable class. We often face tasks like listing out what permission a login has. In my first article in this series, we talked about server role and database role assignment for any given login. But there are cases where login was given more specific permission that was not given through server or database role but through server and database securable. This is the most easily forgotten part of login permission.
It would be nice to simply input the login name that we are interested in and get ready to use script to recover permission on server level and database level securable for the specified principal. In this article, I am only focusing on database and server-level securables. If you are interested to see how to generate login creation, server role assignment or database role assignment, please refer to the first article of the series.
Now, we can look into the script. To use the script, here are the steps. This script works on SQL 2005, SQL 2008 and SQL 2008 R2.
1. Open Microsoft SQL Server Management Studio (a.k.a SSMS), make a connection to the SQL instance, then click New Query. Make sure the credential you use to run the code has adequate privileges to generate the script.
2. Copy below code into the query window
3. Replace 'YourloginName' with the login you would like to re-generate securable permission script, then execute
----------------------------------------------
--Login Pre-requisites
----------------------------------------------
set concat_null_yields_null off
USE master
go
SET NOCOUNT ON
DECLARE @login_name varchar(100)
SET @login_name = 'YourLoginName'
-----------------------------------------------------------------
IF lower(@login_name) IN ('sa','public')
BEGIN
RAISERROR (15405,11,1,@login_name)
RETURN
END
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S'))
BEGIN
PRINT 'Please input valid login name'
RETURN
END
DECLARE @login_sid varbinary(85)
SELECT @login_sid = sid FROM sys.server_principals WHERE name = @login_name
DECLARE @maxid int
IF OBJECT_ID('tempdb..#db_users') is not null
BEGIN
DROP TABLE #db_users
END
SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.databases
WHERE state_desc <> 'OFFLINE'
SELECT @maxid = @@ROWCOUNT
----------------------------------------------
--Create Server Role Temp table
----------------------------------------------
IF OBJECT_ID('tempdb..#srvrole') IS NOT NULL
BEGIN
DROP TABLE #srvrole
END
CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85))
INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember
DECLARE @login_srvrole varchar(1000)
SET @login_srvrole = ''
IF EXISTS (select * from [#srvrole] where ServerRole = 'sysadmin' AND MemberName = @login_name)
BEGIN
PRINT '--Login ['+@login_name+'] is part of sysadmin server role, hence possesses full privileges for SQL instance: '+@@servername
PRINT 'GO'
SELECT @login_srvrole = @login_srvrole + 'EXEC sp_addsrvrolemember '''+MemberName+''','''+ServerRole+''''+CHAR(10) FROM #srvrole
WHERE [MemberName] = @login_name
PRINT @login_srvrole
RETURN
RETURN
END
---------------------------------------------------
--Find out list of db that the login has access to
---------------------------------------------------
PRINT ''
PRINT '----------------------------------------------'
PRINT '--Create database user for login '
PRINT '----------------------------------------------'
IF OBJECT_ID('tempdb..#alldb_users') is not null
BEGIN
DROP TABLE #alldb_users
END
CREATE TABLE #alldb_users(
[dbname] [sysname] NOT NULL,
[name] [sysname] NOT NULL,
[principal_id] [int] NOT NULL,
[type] [char](1) NOT NULL,
[type_desc] [nvarchar](60) NULL,
[default_schema_name] [sysname] NULL,
[create_date] [datetime] NOT NULL,
[modify_date] [datetime] NOT NULL,
[owning_principal_id] [int] NULL,
[sid] [varbinary](85) NULL,
[is_fixed_role] [bit] NOT NULL
)
DECLARE @id int, @sqlcmd varchar(500)
SET @id = 1
WHILE @id <=@maxid
BEGIN
SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id
INSERT INTO #alldb_users EXEC (@sqlcmd)
SET @id = @id + 1
END
DELETE FROM #alldb_users WHERE sid is null
DELETE FROM #alldb_users WHERE sid <> @login_sid
IF NOT EXISTS (SELECT * FROM #alldb_users )
BEGIN
PRINT '--Login ['+@login_name+'] doesnt have access to any database'
END
DECLARE @name sysname, @dbname sysname, @schema sysname, @dbuser_cmd varchar(8000)
DECLARE dbuser_cursor CURSOR FAST_FORWARD FOR
SELECT dbname, name, default_schema_name
FROM #alldb_users
OPEN dbuser_cursor
FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema
WHILE @@FETCH_STATUS = 0
BEGIN
IF @schema IS NOT NULL
BEGIN
SELECT @dbuser_cmd = 'USE ['+dbname+']
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+name+''')
BEGIN
CREATE USER ['+@name+'] FOR LOGIN ['+@login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+'
END
GO
' FROM #alldb_users WHERE name = @name and dbname = @dbname
END
ELSE
BEGIN
SELECT @dbuser_cmd = 'USE ['+dbname+']
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '''+name+''')
BEGIN
CREATE USER ['+@name+'] FOR LOGIN ['+@login_name+']
END
GO
' FROM #alldb_users WHERE name = @name and dbname = @dbname
END
print @dbuser_cmd
FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema
END
CLOSE dbuser_cursor
DEALLOCATE dbuser_cursor
----------------------------------------------
--Create DB Role Temp table
----------------------------------------------
IF OBJECT_ID('tempdb..#dbrole') is not null
BEGIN
DROP TABLE #dbrole
END
create table #dbrole (id int identity(1,1), dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85),default_schema_name varchar(100), login_name varchar(100), db_principal_id int)
DECLARE @dbrole_sqlcmd varchar(max)
DECLARE dbrole_cursor CURSOR FAST_FORWARD FOR
SELECT
'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name, b.principal_id as login_name
from ['+dbname+'].sys.database_role_members a
inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id
inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id
left join sys.server_principals d on b.sid = d.sid
where d.name= '''+@login_name+''''
from #alldb_users
OPEN dbrole_cursor
FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dbrole (dbname, dbrole, dbrole_member, sid, default_schema_name, login_name, db_principal_id) exec(@dbrole_sqlcmd)
FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd
END
CLOSE dbrole_cursor
DEALLOCATE dbrole_cursor
DELETE FROM #dbrole WHERE sid <> @login_sid
IF EXISTS (SELECT * FROM #dbrole where dbrole = 'db_owner')
BEGIN
PRINT '----------------------------------------------'
PRINT'--Login is db_owner of below databases'
PRINT'----------------------------------------------'
END
DECLARE @dbname_dbowner varchar(100), @dbrole_member varchar(100)
DECLARE dbowner_cursor CURSOR FAST_FORWARD FOR
SELECT dbname, dbrole_member from #dbrole where dbrole = 'db_owner'
OPEN dbowner_cursor
FETCH NEXT FROM dbowner_cursor INTO @dbname_dbowner, @dbrole_member
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'USE ['+@dbname_dbowner+']
EXEC sp_addrolemember ''db_owner'','''+@dbrole_member +'''
GO'
FETCH NEXT FROM dbowner_cursor INTO @dbname_dbowner, @dbrole_member
END
CLOSE dbowner_cursor
DEALLOCATE dbowner_cursor
--------------------------------------------------------------------------------------------------------
--Find out what database the login has permission to access (avoid restricted and single user database)
--------------------------------------------------------------------------------------------------------
DELETE From #srvrole where MemberName <> @login_name
IF OBJECT_ID('tempdb..#alldb_users_access') IS NOT NULL
BEGIN
DROP TABLE #alldb_users_access
END
SELECT a.*, collation_name INTO #alldb_users_access FROM #alldb_users a inner join sys.databases b ON a.dbname = b.name
WHERE user_access = 0
OR
(user_access = 2 and exists (SELECT * FROM #srvrole WHERE ServerRole in ('dbcreator','sysadmin')))
OR
(user_access = 2 and a.dbname in (SELECT dbname FROM #dbrole WHERE dbrole = 'db_owner' AND login_name = @login_name))
--------------------------------------------------------------------------------------------------------
--Remove database that login doesnt have permission to connect
--------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#dbconnect') is not null
BEGIN
DROP TABLE #dbconnect
END
CREATE TABLE #dbconnect ( dbname varchar(100), connect_status bit)
DECLARE @dbconnect_sqlcmd varchar(1000)
SET @dbconnect_sqlcmd = ''
DECLARE dbbconnect_cursor CURSOR FAST_FORWARD FOR
SELECT 'select distinct '''+dbname+''', 1 from ['+dbname+'].sys.database_permissions a
inner join ['+dbname+'].sys.database_principals b on a.grantee_principal_id = b.principal_id
inner join ['+dbname+'].sys.server_principals c on b.sid = c.sid
where c.name = '''+@login_name+''''
from #alldb_users_access
OPEN dbbconnect_cursor
FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dbconnect exec( @dbconnect_sqlcmd)
FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd
END
CLOSE dbbconnect_cursor
DEALLOCATE dbbconnect_cursor
insert into #dbconnect
select a.dbname, 0 from #alldb_users_access a left join #dbconnect b on a.dbname = b.dbname
where b.dbname is null
---------------------------------------------------------------------------------------
-- Grant all securable class to login
----------------------------------------------------------------------------------------
PRINT ''
PRINT '----------------------------------------------'
PRINT '--Grant all securable class to login '
PRINT '----------------------------------------------'
IF OBJECT_ID('tempdb..#securable_class') is not null
BEGIN
DROP TABLE #securable_class
END
IF OBJECT_ID('tempdb..#dblevel') is not null
BEGIN
DROP TABLE #dblevel
END
create table #dblevel (login_name varchar(256), dbname sysname, dbuser_name varchar(100), class_desc varchar(100), permission_name varchar(100),state_desc varchar(100))
DECLARE @dblevel_sqlcmd varchar(1000)
DECLARE dblevel_cursor CURSOR FAST_FORWARD FOR
SELECT 'select '''+@login_name+''' as login_name, '''+dbname+''' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc from ['+dbname+'].sys.database_permissions a inner join ['+dbname+'].sys.database_principals b
on a.grantee_principal_id = b.principal_id
where b.name in (''public'','''+name+''') and class_desc = ''DATABASE'''
FROM #alldb_users_access
union
SELECT 'select '''+@login_name+''' as login_name, ''master'' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc from sys.server_permissions a inner join sys.server_principals b
on a.grantee_principal_id = b.principal_id
where b.name = '''+@login_name+''''
UNION
SELECT 'select '''+@login_name+''' as login_name, ''master'' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc from sys.server_permissions a inner join sys.server_principals b
on a.grantee_principal_id = b.principal_id and class_desc = ''SERVER''
where b.name = ''public'''
OPEN dblevel_cursor
FETCH NEXT FROM dblevel_cursor INTO @dblevel_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dblevel (login_name, dbname, dbuser_name, class_desc, permission_name, state_desc) EXEC (@dblevel_sqlcmd)
FETCH NEXT FROM dblevel_cursor INTO @dblevel_sqlcmd
END
CLOSE dblevel_cursor
DEALLOCATE dblevel_cursor
SET NOCOUNT ON
DELETE FROM #dblevel WHERE permission_name IN ('SELECT','INSERT','UPDATE','DELETE','REFERENCES')
DELETE FROM #dblevel WHERE dbname IN (SELECT dbname FROM #dbrole WHERE sid = @login_sid AND dbrole = 'db_owner')
DECLARE @securable_sqlcmd varchar(150)
DECLARE securable_cursor CURSOR FAST_FORWARD FOR
SELECT distinct 'USE ['+dbname+']
GRANT '+permission_name+' TO ['+@login_name+']
GO
' FROM #dblevel
OPEN securable_cursor
FETCH NEXT FROM securable_cursor INTO @securable_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @securable_sqlcmd
FETCH NEXT FROM securable_cursor INTO @securable_sqlcmd
END
CLOSE securable_cursor
DEALLOCATE securable_cursor
If you input a bad login name to the script, it will produce an exception; below are the scenarios.
Scenario 1: If you enter either 'sa' or 'public', then you will see results similar to the below snapshot in the SSMS result pane (Fig. 1.1).
Fig 1.1
Scenario 2: If the login you provide doesn't exist on the SQL instance, then you will see results similar to the below snapshot in the SSMS result pane (Fig 1.2)
Fig 1.2
Now we can look into the real scenario about a valid login with permissions on server and database-level securables. By replacing 'YourLoginName' with a valid login, below is an example of how the outcome of the script will look. It contains the creation of the database user for the login and the permission setting for server and database securables. If a login is db_owner for any database or is part of sysadmin server role member, then the resulting script will only contain the execution of system stored procedure sp_addrolemember and sp_addsrvrolemember instead of printing out granting securables one by one.
Fig 1.3
Scenario 4: Input login is member of db_owner on AdventureWorksDW, not for the rest of the databases (Fig 1.4)
----------------------------------------------
--Create database user for login
----------------------------------------------
USE [master]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'chsu')
BEGIN
CREATE USER [chsu] FOR LOGIN [chsu] WITH DEFAULT_SCHEMA=[dbo]
END
GO
USE [AdventureWorks]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'chsu')
BEGIN
CREATE USER [chsu] FOR LOGIN [chsu] WITH DEFAULT_SCHEMA=[dbo]
END
GO
USE [Northwind]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'chsu')
BEGIN
CREATE USER [chsu] FOR LOGIN [chsu] WITH DEFAULT_SCHEMA=[dbo]
END
GO
USE [AdventureWorksDW]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'chsu')
BEGIN
CREATE USER [chsu] FOR LOGIN [chsu] WITH DEFAULT_SCHEMA=[dbo]
END
GO
----------------------------------------------
--Login is db_owner of below databases
----------------------------------------------
USE [AdventureWorksDW]
EXEC sp_addrolemember 'db_owner','user_dbo_bond_bg'
GO
----------------------------------------------
--Grant all securable class to login
----------------------------------------------
USE [Northwind]
GRANT ALTER ANY APPLICATION ROLE TO [chsu]
GO
USE [Northwind]
GRANT ALTER ANY ROUTE TO [chsu]
GO
USE [Northwind]
GRANT CONNECT TO [chsu]
GO
USE [Northwind]
GRANT CREATE ROUTE TO [chsu]
GO
USE [Northwind]
GRANT SHOWPLAN TO [chsu]
GO
USE [master]
GRANT ALTER ANY LINKED SERVER TO [chsu]
GO
USE [master]
GRANT ALTER SETTINGS TO [chsu]
GO
USE [master]
GRANT CONNECT SQL TO [chsu]
GO
USE [master]
GRANT CONNECT TO [chsu]
GO
USE [master]
GRANT EXTERNAL ACCESS ASSEMBLY TO [chsu]
GO
USE [master]
GRANT UNSAFE ASSEMBLY TO [chsu]
GO
USE [master]
GRANT VIEW ANY DATABASE TO [chsu]
GO
USE [AdventureWorks]
GRANT CONNECT TO [chsu]
GO
USE [AdventureWorks]
GRANT SHOWPLAN TO [chsu]
GO
Fig 1.4
Conclusion
We have covered login regeneration in the first article with login creation, server-level role and database-level role assignment. In this artcile, we have summarized how to produce script for login on server-level and database-level securables.
In my final article of the series, I'll tackle the login generation for the finest scope securable – object-level permission for the login.
'Brain Trainning > DataBase' 카테고리의 다른 글
[MSSQL] MSSQL 속이 궁금할때... Internal View (0) | 2011.03.02 |
---|---|
[MSSQL] 압축된 테이블 및 인덱스 만들기 (0) | 2011.03.02 |
[MSSQL] 인덱스 사용률? (0) | 2011.02.24 |
[MSSQL] Transact SQL Code Library (0) | 2011.02.24 |
[MSSQL] Why is my transaction log full? (0) | 2011.02.21 |