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