SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script Login Roles Permissions in all DBSs


Script Login Roles Permissions in all DBSs

Author
Message
TDuffy
TDuffy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 57
Comments posted to this topic are about the item Script Login Roles Permissions in all DBSs



mariann harper
mariann harper
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 215
I can't this to return anything for SQL 2000.
darklord545
darklord545
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
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/:

------------------------------------------------------------------------------------
-- 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


ckingtaylor
ckingtaylor
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 167
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 + ','
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search