Hi Guys,
Been so long I did post something. Well I really need to work on my clould blog as well but for today it will be just a simple blog on user security.
I had to run through a lot of hoops so thought I will share the scripts with you. The requirement was to script out the Logins and the related users in the databases from one environment to other.
So here goes the script. You need to replace the [domain\windowslogin] with you DomainLogin for which you want to script out the permission.
IF (SELECT OBJECT_ID('msdb.dbo.storepermission') ) IS NULL
begin
CREATE TABLE msdb.dbo.storepermission (
ServerName sysname,
DBName sysname,
USerName sysname,
TypeOfLogin nVARCHAR(100),
PermissionLevel NVARCHAR(100),
TypeOfRole NVARCHAR(100)
)
END
TRUNCATE TABLE msdb.dbo.storepermission
DECLARE @@login_name sysname ='domain\windowslogin'
--SELECT @@login_name
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE 1=1
AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @@login_name
SELECT @statement = 'use '+@dbname +';'+ 'insert into msdb.dbo.storepermission(ServerName,DBName,USerName,TypeOfLogin,PermissionLevel,TypeOfRole)SELECT
ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=''domain\windowslogin''' -- Change the user name
EXEC sp_executesql @statement
PRINT @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT 'IF NOT EXISTS(SELECT principal_id FROM sys.server_principals WHERE name = ''domain\windowslogin'') BEGIN CREATE LOGIN [domain\windowslogin] FROM WINDOWS END; '
SELECT
--R.name AS server_role
-- , P.name AS role_member ,
'EXEC master..sp_addsrvrolemember N' + '''' + P.name + '''' + ', N' + '''' + R.name + '''' + ';' AS command
FROM sys.server_role_members RM
INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id
INNER JOIN (SELECT principal_id, name FROM sys.server_principals WHERE type_desc = 'SERVER_ROLE') R
ON RM.role_principal_id = R.principal_id
WHERE P.name NOT LIKE '#%'
AND P.name NOT LIKE 'NT %'
AND P.type_desc <> 'SERVER_ROLE'
AND P.name NOT IN ('sa')
AND P.name='domain\windowslogin'
ORDER BY R.[name], P.[name]
SELECT 'use '+DBName+' IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = '''+USerName+' '') BEGIN CREATE USER ['
+USerName+'] FOR LOGIN ['+ USerName+'] END ; EXEC sp_addrolemember N'''+PermissionLevel+''', N'''+USerName+''';'
FROM msdb.dbo.storepermission
Hope this helps you.
Thanks,
Tushar
