November 12, 2013 at 1:44 am
Comments posted to this topic are about the item Script All Logins / Users / and Roles
November 12, 2013 at 3:21 am
Have made a few changes:
Only looks at databases which are online, also added square brackets by the "USE" statements in case a database has an illegal character e.g. a number at the beginning of the name
/****************************************************************
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
Cowboy DBA - 11/12/2013
Only looks at databases which are online, also added square brackets by the
"USE" statements in case a database has an illegal character e.g. a number at the beginning of the name
****************************************************************/
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-2 = MAX(idx)
FROM #SQL
WHILE @Line <= @max-2
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-2 = MAX(idx)
FROM #SQL2
SET @line = 1
WHILE @Line <= @max-2
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 sys.databases
WHERE state_desc = 'ONLINE'
AND name NOT IN ('master','model','msdb','tempdb')
ORDER BY name
SELECT @max-2 = MAX(idx)
FROM #Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL
WHILE @line <= @max-2
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 12, 2013 at 6:12 am
Very nice script.
November 12, 2013 at 7:22 am
Thanks for posting this. I just had to do a cold reinstall and restore of my DB server. Has to reconstruct the users/logins from memory! Now I can just run this periodically to have a backup/restore script.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
November 12, 2013 at 9:57 am
Very nice contribution; a keeper! Thank you for sharing.
Steve
November 13, 2013 at 7:27 am
Greg, Thanks for taking the time to create and share this script. I ran into a few issues though.
1. The existence check: IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'[TestMe]'). The square brackets are treated as a literal and if 'TestMe' does exist, '[TestMe]' does not so the CREATE LOGIN portion will execute and fail.
2. Same thing for the generated code to add a database user to a role. The user gets created in the database but the code generated to add it to a role: EXEC sp_addrolemember N'db_datareader', N'[TestMe]'; fails with error: User or role '[TestMe]' does not exist in this database.
3. Will NOT generate code to add a database user IF the user is not assigned to a database role.
4. Will NOT generate code to add a database user IF the database user name is NOT the same as the logon name.
5. The code generated to add a database user does not seem to reflect the correct schema other than 'dbo'
Thanks again for taking the time to share. Sorry if I sound critical.
Lee
November 13, 2013 at 11:16 am
Nice script.
Already mentioned the spaces in the database names. Fixable with brackets in the USE statements
It has a [dbo] user issue.
Tries to script this:
CREATE USER [dbo] FOR LOGIN [dbo] WITH DEFAULT_SCHEMA=[dbo];
Which does not make sense, where there is not an explicit dbowner assigned.
Have to exclude this 'dbo' user in the internal cursor join:
where mp.[name]<>''dbo''
Then it looks to be working
Thanks
Alex Donskoy
SQL Server DBA
Greenberg Traurig PA
Miami FL
November 14, 2013 at 5:39 am
Why not use sp_helprevlogin to script the logins/users instead? http://support.microsoft.com/kb/918992
Also, any reason why you decided to not include all permissions and only script roles?
November 20, 2013 at 1:46 am
Hi.
I am using SQL Server 2008 R2.
I get the following error when I run the script.
-----------
Msg 207, Level 16, State 1, Line 54
Invalid column name 'idx'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'idx'.
-----------
When setting up the "Collection" section of the "Case insensitive" is set.
That is what I thought.
How do I run the script without error ?
Thanks.
December 18, 2013 at 7:53 am
Is there an option to just limit logins to one database. The reason I am asking because we usually refresh QA database from PROD database. We want to capture current logins for the QA database, overwrite it from PROD DB backup, and then re-apply QA permissions.
Thanks!
April 4, 2014 at 2:35 pm
ggeier (11/14/2013)
Why not use sp_helprevlogin to script the logins/users instead? http://support.microsoft.com/kb/918992Also, any reason why you decided to not include all permissions and only script roles?
sp_helprevlogin doesn't do users; just logins and server roles. The assumption must be that you're coming onto a new server where the user DBs don't yet exist.
Ken
April 7, 2014 at 5:48 am
I don't want db users... just want logins and the server roles.
I found this to do the trick
September 7, 2015 at 5:11 am
Wish I read the comment about adding Square brackets before I did the same
September 8, 2015 at 3:06 pm
Good script, thanks.
September 15, 2015 at 6:24 pm
I made a few more changes. The script now adds grants for each database and disables logins that are disabled. I also removed all temp tables and cursors and installed table variables.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply