November 18, 2014 at 8:52 am
Hello,
I have this original script to script out user permissions from particular user database.
select 'EXEC sp_addrolemember N''' + role_name + ''', N''' + login_name + '''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in ('S','U') and dp.name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type='R' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss
Now I want to put it in cursor to script out permissions from each and every database rather than running it against individual databases.
DECLARE @DB_Name varchar(max)
DECLARE @Command nvarchar(max)
DECLARE @ScriptPer nvarchar(max)
DECLARE @Finalcmd nvarchar(max)
--CREATE TABLE #Test(command varchar(8000))
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
Where dbid NOT IN (1,2,3,4)
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13) + 'DBCC CHECKDB(' + @DB_Name + ') WITH ALL_ERRORMSGS' + CHAR(13)
--DECLARE @Command nvarchar(max)
SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13)
--PRINT @Command
SELECT @ScriptPer = 'select '''EXEC sp_addrolemember N''''' + role_name + ''''', N''''' + login_name + '''''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in (''S'',''U'')
and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type=''R'' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss'
SELECT Finalcmd = @command + @ScriptPer
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
But its not working out, too much of dynamic sql. Can anyone help me? Thanks a lot in advance.
November 18, 2014 at 9:21 am
You had some problems with the number of quotes added.
DECLARE @DB_Name varchar(max)
DECLARE @Command nvarchar(max)
DECLARE @ScriptPer nvarchar(max)
DECLARE @Finalcmd nvarchar(max)
--CREATE TABLE #Test(command varchar(8000))
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
Where dbid NOT IN (1,2,3,4)
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13) + 'DBCC CHECKDB(' + @DB_Name + ') WITH ALL_ERRORMSGS' + CHAR(13)
--DECLARE @Command nvarchar(max)
SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13)
--PRINT @Command
SELECT @ScriptPer = 'select ''EXEC sp_addrolemember N'''''' + role_name + '''''', N'''''' + login_name + '''''''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in (''S'',''U'') and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type=''R'' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss'
SELECT @command = @command + @ScriptPer
--EXEC sp_executesql @Command
PRINT @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
November 18, 2014 at 9:39 am
Thanks a lot for the quick reply but I think i did not explain very well or I did not get what I wanted.
Here is what I want to see in output,
USE [testdb] EXEC sp_addrolemember N'db_reader', N'testuser'
USE [otherdb] EXEC sp_addrolemember N'db_owner', N'otheruser'
So that I can grab the output and just run it in bulk.
Here is what I tried so far, not working but i need to pay around.
SELECT @ScriptPer = 'select ''USE ['' + @DB_Name +'']'' + CHAR(13)''EXEC sp_addrolemember N'''''' + role_name + '''''', N'''''' + login_name + '''''''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in (''S'',''U'') and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type=''R'' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss'
November 18, 2014 at 10:10 am
Yes, the problem was that I commented the EXECUTE and included a PRINT for testing purposes.
I would also include the fully qualified name in your results.
DECLARE @DB_Name varchar(max)
DECLARE @Command nvarchar(max)
DECLARE @ScriptPer nvarchar(max)
DECLARE @Finalcmd nvarchar(max)
--CREATE TABLE #Test(command varchar(8000))
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
Where dbid NOT IN (1,2,3,4)
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13) + 'DBCC CHECKDB(' + @DB_Name + ') WITH ALL_ERRORMSGS' + CHAR(13)
--DECLARE @Command nvarchar(max)
SELECT @Command = 'USE [' + @DB_Name +']' + CHAR(13)
--PRINT @Command
SELECT @ScriptPer = 'select ''EXEC ' + QUOTENAME(@DB_Name) + '.sys.sp_addrolemember N'''''' + role_name + '''''', N'''''' + login_name + '''''''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in (''S'',''U'') and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type=''R'' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss'
SELECT @command = @command + @ScriptPer
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
November 18, 2014 at 10:56 am
Ohh thats a great idea. However I tried something like this and it worked as well but i like ur solution much better. Thanks again.
SELECT @ScriptPer = 'select ''USE [' + @DB_Name +'] ''''EXEC sp_addrolemember N'''''' + role_name + '''''', N'''''' + login_name + '''''''' as sql from
(select dp_login.name as login_name, dp_role.name as role_name from sys.database_role_members drm
inner join (select * from sys.database_principals dp where dp.type in (''S'',''U'') and dp.name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'', ''sys'')) dp_login
on drm.member_principal_id = dp_login.principal_id
inner join (select * from sys.database_principals dp where dp.type=''R'' ) dp_role
on drm.role_principal_id = dp_role.principal_id ) ss'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply