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.

» See All Articles by Columnist Claire Hsu

Claire Hsu
Claire Hsu
Claire Hsu has nearly 10 years' experience in database administration, architecture, engineering and development, ranging from SQL 7.0, 2000, 2005 and 2008 environments. Currently, she is a senior DBA for a global infrastructure team in an investment management firm located in New York City. She is certified in MCP, OCP 10G, MCTS and MCITP on SQL Server 2005, and has a master's in Electrical Engineering from University of Massachusetts, Amherst. Her focus as DBA includes project management, handling critical projects like server upgrades, migration, high availability features deployment, performance tuning, monitoring, ensuring business continuity, and client facing and engineering automated processes.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles