Today's script will grant membership in the db_owner fixed database role in all online read/writable user databases to all principals not in the sysadmin fixed server role. I have found this script useful when setting up development servers where all developers are given this level of access. This could however be modified to grant other permissions as required. Have a look and a play 😉
/*
-----------------------------------------------------------------
Grant membership in the db_owner fixed database role in all online read/writable user databases to all principals not in the sysadmin fixed server role
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Declare varibles
DECLARE @DatabaseName VARCHAR(255)
DECLARE @PrincipalName VARCHAR(255)
DECLARE @MinDBID INT
DECLARE @MaxDBID INT
DECLARE @MinPrincipalID INT
DECLARE @MaxPrincipalID INT
DECLARE @SQL VARCHAR(255)
-- Drop temporary tables if they exist
IF OBJECT_ID('tempDB.dbo.#Principal') IS NOT NULL
DROP TABLE #Principal ;
IF OBJECT_ID('tempDB.dbo.#SysAdmin') IS NOT NULL
DROP TABLE #SysAdmin ;
IF OBJECT_ID('tempDB.dbo.#DBOwner') IS NOT NULL
DROP TABLE #DBOwner ;
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
DROP TABLE #Database ;
-- Create temporary tables
CREATE TABLE #Principal
(
ID INT IDENTITY(1, 1),
PrincipalName VARCHAR(100)
)
CREATE TABLE #SysAdmin
(
ID INT IDENTITY(1, 1),
PrincipalName VARCHAR(100)
)
CREATE TABLE #DBOwner
(
ID INT IDENTITY(1, 1),
PrincipalName VARCHAR(100)
)
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
)
--Insert Online, Read/Writable database names into temporary table
IF EXISTS ( SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution', 'reports', 'reportserver',
'reportservertempdb' )
AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE' )
BEGIN
INSERT INTO #Database ( DatabaseName )
SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution', 'reports',
'reportserver', 'reportservertempdb' )
AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
END
-- Get all server principals
IF EXISTS ( SELECT sp.name
FROM sys.server_principals sp
WHERE sp.[type] IN ( 'U', 'G' )--Windows User or Group]
AND sp.[is_disabled] = 0
AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT' ) )
BEGIN
INSERT INTO #Principal ( PrincipalName )
SELECT sp.name
FROM sys.server_principals sp
WHERE sp.[type] IN ( 'U', 'G' )--Windows User or Group]
AND sp.[is_disabled] = 0
AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT' )
END
-- Get all principals who are members of the sysadmin fixed server role
IF EXISTS ( SELECT sp.name
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp ON sp.principal_id = srm.member_principal_id
WHERE sp.[type] IN ( 'U', 'G' )--Windows User or Group]
AND sp.[is_disabled] = 0
AND srm.role_principal_id = 3 --sysadmin
AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT' ) )
BEGIN
INSERT INTO #SysAdmin ( PrincipalName )
SELECT sp.name
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp ON sp.principal_id = srm.member_principal_id
WHERE sp.[type] IN ( 'U', 'G' )--Windows User or Group]
AND sp.[is_disabled] = 0
AND srm.role_principal_id = 3 --sysadmin
AND sp.[name] NOT IN ( 'NT AUTHORITY\SYSTEM',
'NT SERVICE\MSSQLSERVER',
'NT SERVICE\SQLSERVERAGENT' )
END
-- Only interested in principals which arn't members of sysadmin
DELETE FROM #Principal
WHERE PrincipalName IN ( SELECT PrincipalName
FROM #SysAdmin )
-- Set variables for loop
SELECT @MinDBID = MIN(ID),
@MaxDBID = MAX(ID)
FROM #Database
-- Begin loop
WHILE @MinDBID <= @MaxDBID
BEGIN
-- Get DatabaseName
SELECT @DatabaseName = DatabaseName
FROM #Database
WHERE ID = @MinDBID
-- Empty table ready for next database
TRUNCATE TABLE #DBOwner
-- Get all principals who are members of the db_owner fixed database role
IF EXISTS ( SELECT u.name AS Principal
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
INNER JOIN sysusers u ON u.uid = drm.member_principal_id
WHERE dp.name = 'db_owner'
AND dp.name <> 'dbo'
AND u.isntuser = 0 )
BEGIN
INSERT INTO #DBOwner ( PrincipalName )
SELECT u.name AS Principal
FROM sys.database_role_members drm
INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id
INNER JOIN sysusers u ON u.uid = drm.member_principal_id
WHERE dp.name = 'db_owner'
AND dp.name <> 'dbo'
AND u.isntuser = 0
END
-- Set Variables for loop
SELECT @MinPrincipalID = MIN(ID),
@MaxPrincipalID = MAX(ID)
FROM #Principal
-- Begin loop
WHILE @MinPrincipalID <= @MaxPrincipalID
BEGIN
-- Get DatabaseName
SELECT @PrincipalName = PrincipalName
FROM #Principal
WHERE ID = @MinPrincipalID
-- Build up SQL string
SET @SQL = 'USE ' + @DatabaseName + ';' + 'EXEC sp_addrolemember N''db_owner'', N''' + @PrincipalName + ''''
-- Grant membership in db_owner fixed database role
EXEC (@SQL)
-- Get next Principal ID
SET @MinPrincipalID = @MinPrincipalID + 1
END
-- Get next Database ID
SET @MinDBID = @MinDBID + 1
END
Enjoy!
Chris
