Category Archives: Security Management

SQL Server: Script to Create Replica of Existing Database User

Today, I would like to share a script, which is helpful to create a clone of an existing database user, with all of its rights/permissions and securables. This script was originally written by GREG LARSEN for SQL Server 2000, I just made necessary changes so it can be used for SQL Server 2008.

CREATE PROCEDURE USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN
@OLDLOGIN VARCHAR(200), -- EXISTING LOGIN NAME (COPY FROM)
@NEWLOGIN VARCHAR(200), -- NEW LOGIN NAME (COPY TO)
@NEWUSER VARCHAR(128), -- NEW USER NAME 
@PASSWORD VARCHAR(200) = '', -- PASSWORD FOR SQL SERVER AUTHENTICATED USERS
@CREATE_SCRIPT_ONLY BIT = 1 -- 1 IF TO GET ONLY TSQL SCRIPT, 0 IF WANT TO CREATE USER DIRECTLY 
AS -- 
DECLARE @INDX INT 
SET NOCOUNT ON 
DECLARE @TEXT CHAR(100) 
DECLARE @CNT INT
DECLARE @CMD NVARCHAR(200)
DECLARE @DB NVARCHAR(128) 
DECLARE @OLDUSER VARCHAR(100)
-- Temp Table to hold generated commands
CREATE TABLE #TMP_LOGIN_RIGHTS ( RIGHTS_TEXT VARCHAR(MAX) )
----------------------------------------------------------------------------- 
-- Check if given OldLogin exists
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
IF @CNT = 0 
BEGIN
RAISERROR ( '@OLDLOGIN IS NOT A VALID USER OF SQL SERVER', 16, 1 )
RETURN
END
-- Check if given NewUser allready exists on server
SELECT  @CNT = COUNT(*)
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @NEWLOGIN
--
-- IF @NEWLOGIN EXIST ABORT
IF @CNT > 0 
BEGIN
RAISERROR ( '@NEWLOGIN ALREADY EXISTS ON SQL SERVER', 16, 1 ) 
RETURN
END
-- IF @NEWLOGIN CONTAINS '\' THEN NT LOGIN
SELECT  @INDX = CHARINDEX('\', @NEWLOGIN)
IF @INDX > 0 
-- GENERATE COMMANDS TO ADD NT USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_GRANTLOGIN] ''' + @NEWLOGIN
+ '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
ELSE 
BEGIN
IF @PASSWORD = '' 
BEGIN
RAISERROR ( '@PASSWORD MUST BE SPECIFIED FOR SQL SERVER AUTHENTICATION',
16, 1 ) 
RETURN
END -- 
-- GENERATE COMMANDS TO ADD SQL SERVER AUTHENTICATION USER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDLOGIN] '''
+ @NEWLOGIN + ''',''' + @PASSWORD + '''' + CHAR(13)
+ 'EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] '''
+ @NEWLOGIN + ''',''' + RTRIM(DBNAME) + '''' AS RIGHTS_TEXT
FROM    [MASTER].[DBO].[SYSLOGINS]
WHERE   LOGINNAME = @OLDLOGIN
END
-------------------------------------------------------------------------------
SET NOCOUNT ON 
SET @CMD = '[MASTER].[DBO].[SP_HELPUSER]'
-- GET THE NAME OF ALL DATABASES
DECLARE ALLDATABASES CURSOR
FOR SELECT  NAME
FROM    [MASTER].[DBO].[SYSDATABASES] 
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
-- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE
CREATE TABLE #TMPUSERS
(
USERNAME VARCHAR(100),
GROUPNAME VARCHAR(100),
LOGINNAME VARCHAR(100),
DEFDBNAME VARCHAR(100),
USERID CHAR(10),
SCHEMANAME VARCHAR(100),
SUSERID SMALLINT
)
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- COMMAND TO RETURN ALL USERS IN DATABASE
SET @CMD = '[' + @DB + ']' + '.[DBO].[SP_HELPUSER]'
-- GET ALL USERS IN DATABASE INTO TEMPORARY TABLE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,
USERID,SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER OLD USER IS IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
-- IF OLD USER IS IN DATABASE THEN ADD NEW USER TO DATABASE
IF @CNT > 0 
BEGIN
-- DETERMINE IF @NEWUSER ALREADY EXIST IN DATABASE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   USERNAME = @NEWUSER
-- IF USER EXIST ABORT
IF @CNT > 0 
BEGIN
-- CLOSE AND DEALLOCATE CURSOR OF DATABASES SO NEXT TIME AROUND NO ERROR OCCURS
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- SET TEXT OF ERROR MESSAGE
SET @TEXT = '@NEWUSER ALREADY EXIST IN DATABASE '
+ @DB
-- RAISE ERROR AND RETURN
RAISERROR ( @TEXT, 16, 1 )
RETURN
END
-- GENERATE COMMAND TO ADD @NEWLOGIN TO CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [' + @DB
+ '].[DBO].[SP_GRANTDBACCESS] '''
+ @NEWLOGIN + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    ( SELECT DISTINCT
USERNAME,
LOGINNAME
FROM      #TMPUSERS
WHERE     LOGINNAME = @OLDLOGIN
) A
END
-- TRUNCATE TABLE FOR NEXT DATABASE
TRUNCATE TABLE #TMPUSERS
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
-- CLOSE CURSOR OF DATABASES
CLOSE ALLDATABASES
--------------------------------------------------------------------------------
OPEN ALLDATABASES
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPUSER]'
-- EMPTY TEMPORARY TABLE #TMPUSERS
TRUNCATE TABLE #TMPUSERS
-- GET USER PERMISSIONS FOR ALL USERS IN CURRENT DATABASE
INSERT  INTO #TMPUSERS (USERNAME,GROUPNAME,LOGINNAME,DEFDBNAME,USERID,
SCHEMANAME,SUSERID)
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPUSERS
WHERE   LOGINNAME = @OLDLOGIN
AND GROUPNAME  'PUBLIC'
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWUSER TO APPROPRIATE ROLES IN CURRENT DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT DISTINCT
'EXECUTE [' + @DB
+ '].[DBO].[SP_ADDROLEMEMBER] '''
+ RTRIM(A.GROUPNAME) + ''',''' + RTRIM(@NEWUSER)
+ '''' AS RIGHTS_TEXT
FROM    #TMPUSERS A
WHERE   A.LOGINNAME = @OLDLOGIN
AND A.GROUPNAME  'PUBLIC' 
-- GET NEXT DATABASE
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DROP TABLE #TMPUSERS
-----------------------------------------------------------------------------
-- CREATE TABLE TO HOLD SERVER ROLES
CREATE TABLE #TMPSRVROLES
(
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY(85)
)
-- COMMAND TO GET SERVER ROLES
SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
-- GET SERVER ROLES INTO TEMPORARY TABLE
INSERT  INTO #TMPSRVROLES
EXEC ( @CMD
)
-- DETERMINE WHETHER THE OLD USER IS IN A SERVER ROLE
SELECT  @CNT = COUNT(*)
FROM    #TMPSRVROLES
WHERE   MEMBERNAME = @OLDLOGIN
-- IF OLD USER IS IN A ROLE THEN ADD NEW USER TO THAT SERVER ROLE
IF @CNT > 0
-- GENERATE COMMANDS TO ADD @NEWLOGIN INTO THE APPROPRIATE SERVER ROLES
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'EXECUTE [MASTER].[DBO].[SP_ADDSRVROLEMEMBER] ' + ''''
+ RTRIM(@NEWLOGIN) + '''' + ',[' + RTRIM(A.SERVERROLE)
+ ']' AS RIGHTS_TEXT
FROM    #TMPSRVROLES A
WHERE   A.MEMBERNAME = @OLDLOGIN 
-- DROP SERVER ROLE TABLE
DROP TABLE #TMPSRVROLES
-- GRANT USER PERMISSIONS TO OBJECTS AND STATEMENTS
-------------------------------------------------------------------------------
-- CREATE TEMPORARY TABLE TO HOLD INFORMATION ABOUT OBJECTS PERMISSIONS
CREATE TABLE #TMPPROTECT
(
OWNER VARCHAR(100),
OBJECT VARCHAR(100),
GRANTEE VARCHAR(100),
GRANTOR VARCHAR(100),
PROTECTTYPE CHAR(10),
ACTION VARCHAR(20),
COLUMNX VARCHAR(100)
)
OPEN ALLDATABASES
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
FETCH NEXT FROM ALLDATABASES INTO @DB
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- INITIALIZE @OLDUSER VARIABLE 
SET @OLDUSER = '' 
--GENERATE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
SET @CMD = 'SELECT @OLDUSER=NAME FROM [' + @DB
+ '].[DBO].[SYSUSERS] WHERE SID = (SELECT SID FROM [MASTER].[DBO].[SYSLOGINS] 
WHERE LOGINNAME = '
+ CHAR(39) + @OLDLOGIN + CHAR(39) + ')' 
-- EXECUTE COMMAND TO SET @OLDUSER TO THE USER NAME IN DATABASE 
-- IF @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
EXEC [MASTER].[DBO].[SP_EXECUTESQL] @CMD,
N'@OLDUSER CHAR(200) OUTPUT', @OLDUSER OUT
-- IF @OLDUSER IS NOT BLANK THEN @OLDLOGIN HAS ACCESS TO CURRENT DATABASE
IF @OLDUSER  '' 
BEGIN
-- GENERATE COMMAND TO GET OBJECT PERMISSIONS FOR CURRENT DATABASE
SET @CMD = '[' + @DB + '].[DBO].[SP_HELPROTECT]'
-- GET OBJECT PERMISSIONS INTO TEMPORARY TABLE
INSERT  INTO #TMPPROTECT
EXEC ( @CMD
)
-- DETERMINE IF THERE ARE ANY OBJECT PERMISSIONS FOR @OLDUSER
SELECT  @CNT = COUNT(*)
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER 
IF @CNT > 0 
-- SWITCH TO THE APPROPRIATE DATABASE
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'USE [' + @DB + ']'
-- GENERATE COMMANDS TO GRANT OBJECTS PERMISSIONS FOR REFERENCES, SELECT, UPDATE TO @NEWUSER
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT  '.'
AND COLUMNX = '(ALL+NEW)'
-- GRANT COLUMN PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + ']([' + COLUMNX
+ '])' + ' TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT  '.'
AND COLUMNX  '(ALL+NEW)'
AND COLUMNX  '.'
-- GRANT INSERT, DELETE, AND EXECUTE PERMISSION ON OBJECTS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  CASE WHEN RTRIM(PROTECTTYPE) = 'GRANT_WGO'
THEN 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
+ ' WITH GRANT OPTION'
ELSE 'GRANT ' + ACTION + ' ON ['
+ @DB + '].[' + OWNER + '].['
+ OBJECT + '] TO ['
+ RTRIM(@NEWUSER) + ']'
END AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT  '.'
AND ACTION IN ( 'INSERT', 'DELETE',
'EXECUTE' )
AND COLUMNX = '.'
-- GRANT STATEMENT PERMISSIONS
INSERT  INTO #TMP_LOGIN_RIGHTS
SELECT  'GRANT ' + ACTION + ' TO ['
+ RTRIM(@NEWUSER) + ']' AS RIGHTS_TEXT
FROM    #TMPPROTECT
WHERE   GRANTEE = @OLDUSER
AND OBJECT = '.'
-- REMOVE RECORDS FOR TEMPORARY TABLE IN PREPARATION FOR THE NEXT DATABASE TO BE PROCESSES
TRUNCATE TABLE #TMPPROTECT
END
-- GET NEXT DATABASE TO PROCESS
FETCH NEXT FROM ALLDATABASES INTO @DB
END -- WHILE (@@FETCH_STATUS = 0)
CLOSE ALLDATABASES
DEALLOCATE ALLDATABASES
-- DROP TEMPORARY TABLE THAT HELD OBJECT PERMISSIONS
DROP TABLE #TMPPROTECT
---------------------------------------------------------------------------------
-- GET ALL THE GENERATED COMMANDS
DECLARE COMMANDS CURSOR
FOR SELECT  *
FROM    #TMP_LOGIN_RIGHTS
OPEN COMMANDS
FETCH NEXT FROM COMMANDS INTO @CMD
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @CREATE_SCRIPT_ONLY = 1
PRINT @CMD
ELSE
EXEC (@CMD)
FETCH NEXT FROM COMMANDS INTO @CMD
END 
CLOSE COMMANDS
DEALLOCATE COMMANDS
--DROP TEMPORARY TABLES
DROP TABLE #TMP_LOGIN_RIGHTS

Advertisements

>Sql Server: Hide Database Name from Specific User in SSMS

>

Recently,I have tried my best to find out a way to hide name of databases from Sql Server Management Studio, which I don’t want to show to a specific user. But unfortunately I can’t find any proper way to achieve my goal.
You can hide all databases from a specific user by using following statement
USE MASTER
DENY VIEW ANY DATABASE TO [TargetUserNameHere];
And same way by using GRANT, you can allow your user to view ALL databases.  Optimum solution I have found is to hide database objects (even name of TABLES, VIEW, SPs & FUNCTIONS) ONLY, by using following statement
USE YourDatbaseNameHere
GO
DENY VIEW DEFINITION TO [TargetUserNameHere]
or you can use
DENY VIEW DEFINITION ON DATABASE:: YourDatbaseNameHere TO [TargetUserNameHere]
So is there any way to hide a specific database name & definition (NoT ALL DATABASES) from a specific user?