November 29, 2023 at 1:05 pm
I found this older script by Greg Ryan in 2013 to script out all the logins, permissions, roles. however, i didn't see the role permissions.
Please could some T-SQL guru help me to include the role permissions in this script as well. Truly appreciate your help.
/****************************************************************
This Script Generates A script to Create all Logins, Server Roles
, DB Users and DB roles on a SQL Server
Greg Ryan
10/31/2013
****************************************************************/SET NOCOUNT ON
DECLARE
@sql nvarchar(max)
, @Line int = 1
, @max int = 0
, @@CurDB nvarchar(100) = ''
CREATE TABLE #SQL
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
+ sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
+ sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ', CHECK_EXPIRATION=' + CASE is_expiration_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ', CHECK_POLICY='
+ CASE is_policy_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + '
Go
'
FROM
sys.sql_logins
WHERE
name <> 'sa'
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
+ 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ';
Go
'
FROM
sys.server_principals
WHERE
type IN ( 'U' , 'G' )
AND name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' );
PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL
CREATE TABLE #SQL2
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL2
( xSQL
)
SELECT
'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
+ QUOTENAME(R.name) + ';
GO
'
FROM
sys.server_principals L
JOIN sys.server_role_members RM
ON L.principal_id = RM.member_principal_id
JOIN sys.server_principals R
ON RM.role_principal_id = R.principal_id
WHERE
L.type IN ( 'U' , 'G' , 'S' )
AND L.name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' , 'sa' );
PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL2
SET @line = 1
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL2 AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL2
PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'
--Drop Table #Db
CREATE TABLE #Db
(
idx int IDENTITY
,DBName nvarchar(100)
);
INSERT INTO #Db
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
ORDER BY
name;
SELECT
@Max = MAX(idx)
FROM
#Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL
WHILE @line <= @Max
BEGIN
SELECT
@@CurDB = DBName
FROM
#Db
WHERE
idx = @line
SET @SQL = 'Use ' + @@CurDB + '
Declare @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR
Select ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +
''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go''
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
OPEN cur
FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END
CLOSE cur;
DEALLOCATE cur;';
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
SET @Line = @Line + 1
END
DROP TABLE #Db
November 29, 2023 at 7:53 pm
I think it really depends on the problem you are trying to solve. I am guessing you read the comments to the SSC post here:
https://www.sqlservercentral.com/forums/topic/script-all-logins-users-and-roles
If you are looking to re-create the database level roles from a script, I would like to ask why? what is the benefit to this? All of that data is stored in the database backup. If it is at the server roles, MSDN has a script for that here - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5486ec6b-ee6f-47fb-b1e0-61ba731ad970/migration-of-sql-server-roles-from-one-server-to-another-server-for-all-users-sqlserver-2008-?forum=transactsql. Scroll to the bottom, it is the second last post.
In my experience, I have never needed a script to re-create database level users or roles. I have had to migrate logins between systems, but when I did that, I would refresh the databases from live then re-map the windows users to the windows logins and I'd be done. I've never needed to script out anything like the above at the database level.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 30, 2023 at 2:13 pm
thanks, brian for the response and info.
i'm not looking for DB level permissions. as you said when you do a restore, all the database level users, roles, permissions, other objects are maintained.
however, i'm looking for the server level permissions, logins, roles, roles permissions, roles membership. it will be nice to have a script that i can copy to the new instance.
the script in my original post has most of what i'm looking for but missing on the user defined role permissions. even in the MSDN script you shared is missing.
i have a script that can find the server roles, server role permissions, but i was trying to incorporate that in the original script.
use master
SELECT u.name user_name, p.class_desc permission_class, p.permission_name, p.state_desc permission_state
FROM sys.server_permissions p
INNER JOIN sys.server_principals u ON p.grantee_principal_id=u.principal_id
WHERE u.type_desc='SERVER_ROLE' AND u.is_fixed_role=0
December 6, 2023 at 4:35 pm
https://dbatools.io/ - makes this very simple
You can use 'Sync-DbaLoginPermission' (everything) or 'Export-DbaDbRole' (to get role)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply