Hi guys!
This post is simply on TSQL permissions.
Specifically, how to script out users and logins.
Users are specific to a SQL server instance while Logins can be used across a domain (via Active Directory).
Let's get started on how to script users.
This script has only one step.
--Check if ##stage tables exists, and if it does, drop it If exists (Select name from tempdb..sysobjects where name = '##scriptlogins') Drop Table ##scriptlogins Go --Create table that lists all staging tables to be purged Create table ##scriptlogins (listid int identity(1,1), script nvarchar(max)); Insert into ##scriptlogins SELECT 'Use ['+[name]+ ']' + char(13) +char(10) + ' If exists (Select name from tempdb..sysobjects where name = ''##Users'') Drop Table ##Users If exists (Select name from tempdb..sysobjects where name = ''xcur'') close xcur create table ##users ( row_order int not null identity (1, 1), sqltxt varchar(2000) null ) on [PRIMARY] Declare @UseCMD varchar(255) Set @UseCMD = ''use '' + db_name() + char(13) + char(10) + ''go'' + char(13) + char(10) insert into ##Users values (@usecmd) insert into ##Users select ''If exists (Select name from sysusers where name = ''''''+u.name+'''''') Drop User ['' + u.name + '']'' + char(13) + char(10) + ''go'' --+ '''''', @name_in_db='''''' + u.name + '''''''' + char(13) + char(10) + ''go'' from sysusers u inner join master..syslogins l on l.sid = u.sid where gid!=uid and u.name not in (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') insert into ##Users select ''Create User [''+ u.name + ''] For Login [''+ l.name + '']'' + char(13) +char(10)+''go'' from sysusers u inner join master..syslogins l on l.sid = u.sid where gid!=uid and u.name not in (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') insert into ##Users SELECT pe.state_desc COLLATE DATABASE_DEFAULT + '' '' + pe.permission_name COLLATE DATABASE_DEFAULT + '' to ['' + pr.name COLLATE DATABASE_DEFAULT + '']'' + char(13) +char(10) + ''go'' FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id where pe.permission_name not in (''Select'',''Connect'') Declare @rolename sysname declare xcur cursor for select name from sysusers where issqlrole = 1 or isapprole = 1 open xcur fetch xcur into @rolename while @@fetch_status=0 begin insert ##Users select ''ALTER ROLE ['' + @rolename + ''] ADD MEMBER ['' + u.name + ''] '' + char(13) +char(10) + ''go'' from sysusers u, sysusers g, sysmembers m where g.name=@rolename and g.uid=m.groupuid and g.issqlrole=1 and u.uid=m.memberuid fetch xcur into @rolename end close xcur deallocate xcur Delete from ##Users where sqltxt like ''ALTER ROLE%dbo%'' -- To allow advanced options to be changed. EXEC sp_configure ''show advanced options'', 1 -- To update the currently configured value for advanced options. RECONFIGURE -- To enable the feature. EXEC sp_configure ''xp_cmdshell'', 1 -- To update the currently configured value for this feature. RECONFIGURE select sqltxt from ##Users order by row_order Declare @bcpCMD varchar(200) Set @bcpCMD = ''bcp "select sqltxt from ##Users order by row_order" queryout C:\temp\Permissions2\'' +db_name()+ ''_RestoreUsers.sql -T -c -S SERVER'' exec master..xp_cmdshell @bcpCMD --EXEC sp_configure ''show advanced options'', 1 --GO ---- To update the currently configured value for advanced options. --RECONFIGURE --GO ---- To disable the feature. --EXEC sp_configure ''xp_cmdshell'', 0 --GO ---- To update the currently configured value for this feature. --RECONFIGURE --GO' FROM sys.databases Select * from ##scriptlogins --Loop that goes through the list of tables and truncates them based on the id declare @tableid int declare @sqltext nvarchar (max) Set @tableid = 1 While @tableid <= (Select max(listid) from ##scriptlogins) Begin Set @sqltext = (Select script from ##scriptlogins where listid = @tableid) exec sp_executesql @sqltext Set @tableid = @tableid + 1 End --drop table #scriptlogins
Note: You will need to update the path to the permissions location. The permissions location has your SQL file that will contain all the user permissions for the SQL server instance [currently at c:\Temp\Permissions in the script above]. You will also need to replace the name of the SERVER with your server name.
I.e. queryout C:\temp\Permissions2\'' +db_name()+ ''_RestoreUsers.sql -T -c -S SERVER''
After executing the script, you will now have at the location you specified [c:\Temp\Permissions] a SQL file that has permissions for all users of the SQL instance in addition to individual SQL files that contain user permissions for each database on your SQL server instance.
That's it!
If you have any questions related to this post, please put them below.
Thank you and Happy reading,
-marshé hutchinson
#learnSQLwithme
This post is simply on TSQL permissions.
Specifically, how to script out users and logins.
Users are specific to a SQL server instance while Logins can be used across a domain (via Active Directory).
Let's get started on how to script users.
This script has only one step.
--Check if ##stage tables exists, and if it does, drop it If exists (Select name from tempdb..sysobjects where name = '##scriptlogins') Drop Table ##scriptlogins Go --Create table that lists all staging tables to be purged Create table ##scriptlogins (listid int identity(1,1), script nvarchar(max)); Insert into ##scriptlogins SELECT 'Use ['+[name]+ ']' + char(13) +char(10) + ' If exists (Select name from tempdb..sysobjects where name = ''##Users'') Drop Table ##Users If exists (Select name from tempdb..sysobjects where name = ''xcur'') close xcur create table ##users ( row_order int not null identity (1, 1), sqltxt varchar(2000) null ) on [PRIMARY] Declare @UseCMD varchar(255) Set @UseCMD = ''use '' + db_name() + char(13) + char(10) + ''go'' + char(13) + char(10) insert into ##Users values (@usecmd) insert into ##Users select ''If exists (Select name from sysusers where name = ''''''+u.name+'''''') Drop User ['' + u.name + '']'' + char(13) + char(10) + ''go'' --+ '''''', @name_in_db='''''' + u.name + '''''''' + char(13) + char(10) + ''go'' from sysusers u inner join master..syslogins l on l.sid = u.sid where gid!=uid and u.name not in (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') insert into ##Users select ''Create User [''+ u.name + ''] For Login [''+ l.name + '']'' + char(13) +char(10)+''go'' from sysusers u inner join master..syslogins l on l.sid = u.sid where gid!=uid and u.name not in (''public'',''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') insert into ##Users SELECT pe.state_desc COLLATE DATABASE_DEFAULT + '' '' + pe.permission_name COLLATE DATABASE_DEFAULT + '' to ['' + pr.name COLLATE DATABASE_DEFAULT + '']'' + char(13) +char(10) + ''go'' FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id where pe.permission_name not in (''Select'',''Connect'') Declare @rolename sysname declare xcur cursor for select name from sysusers where issqlrole = 1 or isapprole = 1 open xcur fetch xcur into @rolename while @@fetch_status=0 begin insert ##Users select ''ALTER ROLE ['' + @rolename + ''] ADD MEMBER ['' + u.name + ''] '' + char(13) +char(10) + ''go'' from sysusers u, sysusers g, sysmembers m where g.name=@rolename and g.uid=m.groupuid and g.issqlrole=1 and u.uid=m.memberuid fetch xcur into @rolename end close xcur deallocate xcur Delete from ##Users where sqltxt like ''ALTER ROLE%dbo%'' -- To allow advanced options to be changed. EXEC sp_configure ''show advanced options'', 1 -- To update the currently configured value for advanced options. RECONFIGURE -- To enable the feature. EXEC sp_configure ''xp_cmdshell'', 1 -- To update the currently configured value for this feature. RECONFIGURE select sqltxt from ##Users order by row_order Declare @bcpCMD varchar(200) Set @bcpCMD = ''bcp "select sqltxt from ##Users order by row_order" queryout C:\temp\Permissions2\'' +db_name()+ ''_RestoreUsers.sql -T -c -S SERVER'' exec master..xp_cmdshell @bcpCMD --EXEC sp_configure ''show advanced options'', 1 --GO ---- To update the currently configured value for advanced options. --RECONFIGURE --GO ---- To disable the feature. --EXEC sp_configure ''xp_cmdshell'', 0 --GO ---- To update the currently configured value for this feature. --RECONFIGURE --GO' FROM sys.databases Select * from ##scriptlogins --Loop that goes through the list of tables and truncates them based on the id declare @tableid int declare @sqltext nvarchar (max) Set @tableid = 1 While @tableid <= (Select max(listid) from ##scriptlogins) Begin Set @sqltext = (Select script from ##scriptlogins where listid = @tableid) exec sp_executesql @sqltext Set @tableid = @tableid + 1 End --drop table #scriptlogins
Note: You will need to update the path to the permissions location. The permissions location has your SQL file that will contain all the user permissions for the SQL server instance [currently at c:\Temp\Permissions in the script above]. You will also need to replace the name of the SERVER with your server name.
I.e. queryout C:\temp\Permissions2\'' +db_name()+ ''_RestoreUsers.sql -T -c -S SERVER''
After executing the script, you will now have at the location you specified [c:\Temp\Permissions] a SQL file that has permissions for all users of the SQL instance in addition to individual SQL files that contain user permissions for each database on your SQL server instance.
That's it!
If you have any questions related to this post, please put them below.
Thank you and Happy reading,
-marshé hutchinson
#learnSQLwithme
Comments
Post a Comment