T-SQL Script to get user with DB OWNER role in SQL Server
MSSQL db owner, SQL server, SQL Server database access 0
(0)
Below query can be used to get all database users having db owner roles in SQL Server.
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)
CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE state_desc='online' and database_id >4
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ '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 pp.name=''db_owner'' and p.name<>''dbo'''
INSERT INTO #UserPermission
EXEC sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM #UserPermission
DROP TABLE #UserPermission
Sample Output:
How useful was this post?
Click on a star to rate it!
Average rating 0 / 5. Vote count: 0
No votes so far! Be the first to rate this post.