October 8, 2007 at 10:01 am
Comments posted to this topic are about the item Script Login Roles Permissions in all DBSs
October 31, 2007 at 12:07 pm
I can't this to return anything for SQL 2000.
December 7, 2010 at 9:33 am
VERY useful script! Saved my week!
This should be the code modified for SQL 2005/2008 with updated stored procedures from http://support.microsoft.com/kb/918992/%5B/url%5D:
------------------------------------------------------------------------------------
-- Description: Provide a list of login(s) and create a script to recreate all login and user settings
-- Revision History
-- Date Author Revision Description
-- 10/19/2005 Terry Duffy Original (Expanded from MS code and code from Bradley Morris)
------------------------------------------------------------------------------------
-- Usage
-- Populate @list variable below with account(s),comma delimited list to script.
-- Save output to recreate:Login,Default DB,Server Roles,DB Access,DB Roles,DB Object Permissions.
-- NOTE:
-- Stored procedures are created in Master, but are deleted
/*****************************Start Create needed procedures***************************/
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
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
/*****************************End Create needed procedures***************************/
SET NOCOUNT ON
Declare
@List varchar(8000),
@DatabaseUserName [sysname],
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261),
@DB_Name sysname,
@cmd varchar(8000),
@count int
set @List = 'BBLEARN,BBLEARN_report,BBLEARN_admin,BBLEARN_cms,BBLEARN_stats'
set @List = @List + ','
Create Table ##DB_USERs
(
Name sysname,
DatabaseUserID smallint,
ServerUserName sysname
)
Create Table ##DB_Roles
(
Name sysname
)
CREATE TABLE ##sysobjects (
[name] [sysname] NULL ,
[id] [int] NULL ,
[xtype] [char] (2) NULL ,
[uid] [smallint] NULL ,
[info] [smallint] NULL ,
[status] [int] NULL ,
[base_schema_ver] [int] NULL ,
[replinfo] [int] NULL ,
[parent_obj] [int] NULL ,
[crdate] [datetime] NULL ,
[ftcatid] [smallint] NULL ,
[schema_ver] int NULL ,
[stats_schema_ver] int NULL ,
[type] char(2) NULL ,
[userstat] smallint NULL ,
[sysstat] smallint NULL ,
[indexdel] smallint NULL ,
[refdate] datetime null,
[version] int NULL ,
[deltrig] int NULL ,
[instrig] int NULL ,
[updtrig] int NULL ,
[seltrig] int NULL ,
[category] int NULL ,
[cache] smallint NULL ,
)
CREATE TABLE ##sysprotects (
[id] [int] NOT NULL ,
[uid] [smallint] NOT NULL ,
[action] [tinyint] NOT NULL ,
[protecttype] [tinyint] NOT NULL ,
[columns] [varbinary] (4000) NULL ,
[grantor] [smallint] NOT NULL
)
CREATE TABLE ##SRV_Roles
(
SERVERROLE VARCHAR(100),
MEMBERNAME VARCHAR(100),
MEMBERSID VARBINARY (85)
)
/*Loop thru file_list*/
while @List <> ''
Begin
set @DatabaseUserName = left( @List, charindex( ',', @List ) - 1 )
Print '--*************Begin ' + @DatabaseUserName + ' ************************************'
Print '--********Begin Script the Login ********************************************************'
/*Script login with password*/
Execute sp_help_revlogin @DatabaseUserName
Print 'GO'
/*Script default database*/
Select @cmd = 'EXEC [MASTER].[DBO].[SP_DEFAULTDB] [' + @DatabaseUserName + '],[' + RTRIM(DBNAME) + ']' + char(13) + 'GO'
FROM [MASTER].[DBO].[SYSLOGINS]
WHERE LOGINNAME = @DatabaseUserName
Print '--Assign Default Database'
Print @CMD
/*GET SERVER ROLES INTO TEMPORARY TABLE*/
SET @CMD = '[MASTER].[DBO].[SP_HELPSRVROLEMEMBER]'
INSERT INTO ##SRV_Roles EXEC (@CMD)
Set @CMD = ''
Select @CMD = @CMD + 'EXEC sp_addsrvrolemember @loginame = ' + char(39) + MemberName + char(39) + ', @rolename = ' + char(39) + ServerRole + char(39) + char(13) + 'GO' + char(13)
from ##SRV_Roles where MemberName = @DatabaseUserName
Print '--Assign Server Roles'
Print @CMD
Delete ##SRV_Roles
Print '--********End Script the Login *********************************************************'
Print ''
/*Get a table with dbs where login has access*/
set @DB_Name = ''
Select
@DB_Name = min(name)
from
master..sysdatabases
where
name > @DB_Name
While @DB_Name is not null
Begin
Set @cmd =
'insert ##DB_USERs
SELECT '
+ char(39) + @DB_Name + char(39) + ',' +
'u.[uid],
l.[loginname]
FROM '
+ @DB_Name + '.[dbo].[sysusers] u
INNER JOIN [master].[dbo].[syslogins] l
ON u.[sid] = l.[sid]
WHERE
u.[name] = ' + char(39) + @DatabaseUserName + char(39)
Exec (@cmd)
Select
@DB_Name = min(name)
from
master..sysdatabases
where
name > @DB_Name
End
/*Add users/roles/object permissions to databases*/
set @DB_Name = ''
Select
@DB_Name = min(name)
from
##DB_USERs
where
name > @DB_Name
While @DB_Name is not null
Begin
Print '/************Begin Database ' + @DB_Name + ' ****************/'
select @ServerUserName = ServerUserName,@DatabaseUserID = DatabaseUserID from ##DB_USERs where name = @DB_Name
Set @cmd =
'USE [' + @DB_Name + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO'
Print '--Add user to databases'
Print @cmd
/*Populate roles for this user*/
Select @cmd =
'Insert ##DB_Roles
Select name
FROM '
+ @DB_Name + '.[dbo].[sysusers]
WHERE
[uid] IN (SELECT [groupuid] FROM ' + @DB_Name + '.[dbo].[sysmembers] WHERE [memberuid] = ' + cast(@DatabaseUserID as varchar(25)) + ')'
--Print @cmd
Exec (@cmd)
/*Add user to roles*/
Set @cmd = ''
Select @cmd = isnull(@cmd,'') + 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + Name + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''+ CHAR(13) +
'GO' + CHAR(13)
from ##DB_Roles
if len(@cmd) > 0
Print '--Add user to role(s)'
Print @cmd
Delete ##DB_Roles
/*Object Permissions*/
set @count = 0
Select @cmd =
'Insert ##sysobjects Select * FROM ' + @DB_Name + '.[dbo].[sysobjects]'
Exec (@cmd)
Select @cmd =
'Insert ##sysprotects Select * FROM ' + @DB_Name + '.[dbo].[sysprotects]'
Exec (@cmd)
DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
DISTINCT([##sysobjects].[id]), '[' + USER_NAME([##sysobjects].[uid]) + '].[' + [##sysobjects].[name] + ']'
FROM
[dbo].[##sysprotects]
INNER JOIN [dbo].[##sysobjects]
ON [##sysprotects].[id] = [##sysobjects].[id]
WHERE
[##sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
if @count = 0
Begin
Print '--Assign Object Level Permissions'
set @count = 1
End
SET @cmd = ''
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @cmd = @cmd + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @cmd = @cmd + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @cmd = @cmd + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @cmd = @cmd + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @cmd = @cmd + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @cmd = @cmd + 'REFERENCES,'
IF LEN(@cmd) > 0
BEGIN
IF RIGHT(@cmd, 1) = ','
SET @cmd = LEFT(@cmd, LEN(@cmd) - 1)
SET @cmd = 'GRANT' + CHAR(13) +
CHAR(9) + @cmd + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @cmd + CHAR(13) + 'GO'
END
SET @cmd = ''
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @cmd = @cmd + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @cmd = @cmd + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @cmd = @cmd + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @cmd = @cmd + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @cmd = @cmd + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[##sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @cmd = @cmd + 'REFERENCES,'
IF LEN(@cmd) > 0
BEGIN
IF RIGHT(@cmd, 1) = ','
SET @cmd = LEFT(@cmd, LEN(@cmd) - 1)
SET @cmd = 'DENY' + CHAR(13) +
CHAR(9) + @cmd + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @cmd + CHAR(13) + 'GO'
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
Delete ##sysobjects
Delete ##sysprotects
Print '/************End Database ' + @DB_Name + ' ****************/'
/*next db*/
Select
@DB_Name = min(name)
from
##DB_USERs
where
name > @DB_Name
End
Print '--*************End ' + @DatabaseUserName + ' ************************************'
Print ''
/*Parse the list down*/
set @List = right( @List, datalength( @List ) - charindex( ',', @List ) )
/*Clear data for the last user*/
Delete ##DB_USERs
End
/*Clean up*/
Drop table ##DB_USERs
Drop table ##DB_Roles
Drop table ##sysobjects
Drop table ##sysprotects
Drop table ##SRV_Roles
use master
Drop procedure sp_help_revlogin
Drop procedure sp_hexadecimal
August 11, 2011 at 8:48 am
Additionally you can add this to script ALL logins:
SELECT @List = COALESCE(@List+',' ,'') + loginname from syslogins
set @List = @List + ','
Instead of:
set @List = 'BBLEARN,BBLEARN_report,BBLEARN_admin,BBLEARN_cms,BBLEARN_stats'
set @List = @List + ','
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy