I’m a strong believer of role based security and today proved my point. In a server migration that we have underway, I migrated what I thought was all the permissions for this particular login. As it turned out, someone in the past had granted EXEC permissions on a schema directly to the Windows login. When the developer who was testing in anticipation of the move found that his processes weren’t working, I had to go looking as to why. When permissions are granted directly against a login I don’t think that they are particularly visible and this can lead to things being missed – as I found out.
I used the following script to migrate all the logins and permissions – of course expecting that role based security was in force. In particular, my goal is to duplicate the permissions of one particular login to another login
SET NOCOUNT ON
USE master
GO
DECLARE @dbname VARCHAR(128)
DECLARE dbname_crsr CURSOR
FOR
SELECT name
FROM master.sys.databases
WHERE state = 0
AND user_access = 0
AND is_read_only = 0
AND source_database_id IS NULL --< < database id of the source database when it is a snapshot
ORDER BY database_id ASC
OPEN dbname_crsr
FETCH NEXT FROM dbname_crsr INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(2000)
SELECT @sql = 'SELECT sp.name AS LoginName
, sp.default_database_name
, DB_NAME() AS DBName
, u1.name AS UserName
, u1.default_schema_name
, u2.name AS RoleName
FROM ' + @dbname + '.sys.database_principals AS u1
INNER JOIN sys.server_principals AS sp ON u1.sid = sp.sid
INNER JOIN ' + @dbname + '.sys.database_role_members AS rm ON u1.principal_id = rm.member_principal_id
INNER JOIN ' + @dbname + '.sys.database_principals AS u2 ON rm.role_principal_id = u2.principal_id
WHERE u1.name = ''put your particular windows login here or comment out for all permissions'''
--PRINT (@sql)
EXEC (@sql)
FETCH NEXT FROM dbname_crsr INTO @dbname
END
CLOSE dbname_crsr
DEALLOCATE dbname_crsr
GO