April 29, 2018 at 11:22 pm
Experts,
Do you'll know of a way to get details on how to get the list of users/groups (we have lot of group accounts) that have create table permission or is part of db_ddlamin group or db_owner or sysadmin etc. I would like to get these details all part of a single script that can loop through all the DBs and get me the culprits. Since, I have more than 25 odd databases getting to each one by one would be a pain. It would be really helpful if there is a way to get around it so I can have everything listed in one shot.
Thanks
April 30, 2018 at 5:47 am
Take a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.
April 30, 2018 at 11:12 am
HanShi - Monday, April 30, 2018 5:47 AMTake a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.
Can you provide me the exact script link because I could not locate that script.
April 30, 2018 at 11:25 am
Feivel - Monday, April 30, 2018 11:12 AMHanShi - Monday, April 30, 2018 5:47 AMTake a look at the free scripts of Kenneth Fisher (https://sqlstudies.com/free-scripts/). He has a script to list all database permissions. You can adjust the script by yourself to get only the output to your need.Can you provide me the exact script link because I could not locate that script.
Try this link for his script:
sp_DBPermissions
Sue
April 30, 2018 at 11:45 am
Feivel - Monday, April 30, 2018 11:12 AMHanShi - Monday, April 30, 2018 5:47 AM
Try this link for his script:
sp_DBPermissions
Sue
Thanks for providing the direct link Sue. I wasn't that fast putting a reply together on my mobile.
April 30, 2018 at 11:51 am
Simply querying system tables won't cut it. Check out the sys.fn_my_permissions function which can be used to determine "effective" permissions, meaning it covers permissions inherited by membership in domain, server, or database role.
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql?view=sql-server-2017
Also, near bottom of article note the use of "EXECUTE AS LOGIN" to impersonate an account, which is essential for this.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 30, 2018 at 12:00 pm
Sue_H - Monday, April 30, 2018 11:25 AMFeivel - Monday, April 30, 2018 11:12 AMHanShi - Monday, April 30, 2018 5:47 AMTry this link for his script:
sp_DBPermissionsSue
Thanks for providing the direct link Sue. I wasn't that fast putting a reply together on my mobile.
It was odd - I had it opened in another tab from your link when I saw the question.
Nice bunch of scripts in that link so thanks for posting that. Never ran across it before.
Sue
April 30, 2018 at 12:19 pm
I think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.
DECLARE @db_name AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
'ReportServerTempDB'
)
AND name NOT LIKE '%CDW%'
AND name NOT LIKE '%demo%'; -- exclude these databases
CREATE TABLE #DBRoles
(
Principal VARCHAR(100),
DBRole VARCHAR(100)
);
OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE [' + @db_name + ']';
PRINT (@sql);
EXEC (@sql);
INSERT INTO #DBRoles
(
Principal,
DBRole
)
SELECT P1.name AS Principal,
P2.name AS DBRole
FROM sys.database_principals AS P1
INNER JOIN sys.database_role_members AS RM
ON RM.member_principal_id = P1.principal_id
INNER JOIN sys.database_principals AS P2
ON P2.principal_id = RM.role_principal_id
WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );
FETCH NEXT FROM db_cursor
INTO @db_name;
END;
SELECT * FROM #DBRoles;
DROP TABLE #DBRoles;
CLOSE db_cursor;
DEALLOCATE db_cursor;
April 30, 2018 at 3:03 pm
Feivel - Monday, April 30, 2018 12:19 PMI think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.
DECLARE @db_name AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
'ReportServerTempDB'
)
AND name NOT LIKE '%CDW%'
AND name NOT LIKE '%demo%'; -- exclude these databasesCREATE TABLE #DBRoles
(
Principal VARCHAR(100),
DBRole VARCHAR(100)
);OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @db_name;WHILE @@FETCH_STATUS = 0
BEGINSET @sql = 'USE [' + @db_name + ']';
PRINT (@sql);
EXEC (@sql);INSERT INTO #DBRoles
(
Principal,
DBRole
)
SELECT P1.name AS Principal,
P2.name AS DBRole
FROM sys.database_principals AS P1
INNER JOIN sys.database_role_members AS RM
ON RM.member_principal_id = P1.principal_id
INNER JOIN sys.database_principals AS P2
ON P2.principal_id = RM.role_principal_id
WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );FETCH NEXT FROM db_cursor
INTO @db_name;
END;SELECT * FROM #DBRoles;
DROP TABLE #DBRoles;CLOSE db_cursor;
DEALLOCATE db_cursor;
sp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session. You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database. So you are only ever hitting the current database, over and over again.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
April 30, 2018 at 3:38 pm
Kenneth.Fisher - Monday, April 30, 2018 3:03 PMFeivel - Monday, April 30, 2018 12:19 PMI think I am doing something wrong here. Basically, it is looping through all the DBs but is actually not outputting the entire result set. I am not sure where I am going wrong. Also, I wanted to append the DB name in the column list so that I know where it is coming from.
DECLARE @db_name AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
'ReportServerTempDB'
)
AND name NOT LIKE '%CDW%'
AND name NOT LIKE '%demo%'; -- exclude these databasesCREATE TABLE #DBRoles
(
Principal VARCHAR(100),
DBRole VARCHAR(100)
);OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @db_name;WHILE @@FETCH_STATUS = 0
BEGINSET @sql = 'USE [' + @db_name + ']';
PRINT (@sql);
EXEC (@sql);INSERT INTO #DBRoles
(
Principal,
DBRole
)
SELECT P1.name AS Principal,
P2.name AS DBRole
FROM sys.database_principals AS P1
INNER JOIN sys.database_role_members AS RM
ON RM.member_principal_id = P1.principal_id
INNER JOIN sys.database_principals AS P2
ON P2.principal_id = RM.role_principal_id
WHERE P2.name IN ( 'db_owner', 'db_ddladmin' );FETCH NEXT FROM db_cursor
INTO @db_name;
END;SELECT * FROM #DBRoles;
DROP TABLE #DBRoles;CLOSE db_cursor;
DEALLOCATE db_cursor;sp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session. You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database. So you are only ever hitting the current database, over and over again.
Thanks! I fixed the script but I am not sure how do I input the DB name column.
May 1, 2018 at 1:18 am
Feivel - Monday, April 30, 2018 3:38 PMKenneth.Fisher - Monday, April 30, 2018 3:03 PMsp_executesql (and other forms of dynamic SQL) aren't run in exactly the same context as your session. You are doing the USE inside the dynamic SQL script, but as soon as you exit that script you are back in your current database. So you are only ever hitting the current database, over and over again.Thanks! I fixed the script but I am not sure how do I input the DB name column.
There are two options to get the name of the database in the result. You can use the value of the variable @db_name, which you use to loop through the databases. Personally I prefer to use the function DB_NAME() to return the current/active database name.
To implement this in your script you need to add a column to the temp table and additionally use one of the obove methots to expand the SELECT. The modified script will look something like this:
DECLARE @db_name AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'DBA', 'SSISDB', 'ReportServer',
'ReportServerTempDB'
)
AND name NOT LIKE '%CDW%'
AND name NOT LIKE '%demo%'; -- exclude these databases
CREATE TABLE #DBRoles
(
DBName VARCHAR(100),
Principal VARCHAR(100),
DBRole VARCHAR(100)
);
OPEN db_cursor;
FETCH NEXT FROM db_cursor
INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE [' + @db_name + ']
INSERT INTO #DBRoles
(
DBName,
Principal,
DBRole
)
SELECT DB_NAME() as DBName,
P1.name AS Principal,
P2.name AS DBRole
FROM sys.database_principals AS P1
INNER JOIN sys.database_role_members AS RM
ON RM.member_principal_id = P1.principal_id
INNER JOIN sys.database_principals AS P2
ON P2.principal_id = RM.role_principal_id
WHERE P2.name IN ( ''db_owner'', ''db_ddladmin'' );'
PRINT (@sql);
EXEC (@sql);
FETCH NEXT FROM db_cursor
INTO @db_name;
END;
SELECT * FROM #DBRoles;
DROP TABLE #DBRoles;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply