November 3, 2014 at 10:30 am
Has anyone come across a query to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
Howard
November 3, 2014 at 2:31 pm
Try this
SET NOCOUNT ON
CREATE TABLE #all_users (db VARCHAR(70), sid VARBINARY(85), stat VARCHAR(50))
EXEC master.sys.sp_msforeachdb
'INSERT INTO #all_users
SELECT ''?'', CONVERT(varbinary(85), sid) ,
CASE WHEN r.role_principal_id IS NULL AND p.major_id IS NULL
THEN ''no_db_permissions'' ELSE ''db_user'' END
FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p
ON u.principal_id = p.grantee_principal_id
AND p.permission_name <> ''CONNECT''
LEFT JOIN [?].sys.database_role_members r
ON u.principal_id = r.member_principal_id
WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''
IF EXISTS
(SELECT l.name FROM sys.server_principals l LEFT JOIN sys.server_permissions p
ON l.principal_id = p.grantee_principal_id
AND p.permission_name <> 'CONNECT SQL'
LEFT JOIN sys.server_role_members r
ON l.principal_id = r.member_principal_id
LEFT JOIN #all_users u
ON l.sid= u.sid
WHERE r.role_principal_id IS NULL AND l.type_desc <> 'SERVER_ROLE'
AND p.major_id IS NULL
)
BEGIN
SELECT DISTINCT l.name LoginName, l.type_desc, l.is_disabled,
ISNULL(u.stat + ', but is user in ' + u.db +' DB', 'no_db_users') db_perms,
CASE WHEN p.major_id IS NULL AND r.role_principal_id IS NULL
THEN 'no_srv_permissions' ELSE 'na' END srv_perms
FROM sys.server_principals l LEFT JOIN sys.server_permissions p
ON l.principal_id = p.grantee_principal_id
AND p.permission_name <> 'CONNECT SQL'
LEFT JOIN sys.server_role_members r
ON l.principal_id = r.member_principal_id
LEFT JOIN #all_users u
ON l.sid= u.sid
WHERE l.type_desc <> 'SERVER_ROLE'
AND ((u.db IS NULL AND p.major_id IS NULL
AND r.role_principal_id IS NULL )
OR (u.stat = 'no_db_permissions' AND p.major_id IS NULL
AND r.role_principal_id IS NULL))
ORDER BY 1, 4
END
DROP TABLE #all_users
Igor Micev,My blog: www.igormicev.com
November 3, 2014 at 9:14 pm
Igor Micev (11/3/2014)
Try this...
Reasons not to use sp_msforeachdb:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 4, 2014 at 1:13 am
Orlando Colamatteo (11/3/2014)
Igor Micev (11/3/2014)
Try this...Reasons not to use sp_msforeachdb:
Yes, and thanks for the links. I know there are some issues with sp_msforeachdb. Additionally I've read somewhere that it may skip (or skips) offline databases. However the script is still not removed from sql server and the MS support say that it's for their internal use, not for public. However some public still use it.
Igor Micev,My blog: www.igormicev.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply