Archive for the ‘Security’ Category

How to List the Login’s Databases Access and Database Role(s) on One Row?

Challenge: A SQL Server login has access to 3 databases. The database users will be the same as the login. You have been requested to list the login’s database access on the instance and the role(s) the login’s database user belongs to in each of the databases like the screen-shot blow:
Logins and DB Access and Roles

Here’s the solution. It is based on the blog, http://www.handsonsqlserver.com/how-to-find-all-database-users-and-their-role-membership/ in addition to utilizing the correlated query plus the STUFF() function:

-- find all logins and their database access and role memberships in one row
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


--SELECT dsr.dbname AS [Database Name],
--	dsr.dbuser AS [Database User],
--	dsr.dbrole AS [Database Role],
--	dsr.account_type AS [Account Type],
--	dsr.create_date,
--	dsr.modify_date
--FROM #dbs_users_roles AS dsr;


IF OBJECT_ID('tempdb..#dbs_users') IS NOT NULL
	DROP TABLE #dbs_users;

SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date,
	STUFF((SELECT '; ' + dr.dbrole 
			FROM #dbs_users_roles AS dr
			WHERE dr.dbname = dsr.dbname AND dr.dbuser = dsr.dbuser
			FOR XML PATH('')),1,1,''
	) AS [DB Roles]
INTO #dbs_users 
FROM #dbs_users_roles AS dsr
GROUP BY dsr.dbname, dsr.dbuser, dsr.account_type, dsr.create_date, dsr.modify_date


SELECT u.[Database User],
	u.[Account Type],
	STUFF((SELECT ' ' +'%[DB]: ' +urs.[Database Name]+ ' [DB Role(s)]:' +urs.[DB Roles]
		FROM #dbs_users urs
		WHERE urs.[Database User] = u.[Database User]
		FOR XML PATH('')),1,1,''
	) [DB Access and Roles]
FROM #dbs_users u
GROUP BY u.[Database User], u.[Account Type]

How to List the Server Roles for the Logins in a Row Separated by a Delimiter?

You have met the challenge to create the report to show all the logins and their corresponding servers roles. Now, some very wise people with pay grades much higher than yours have requested that you need to format the report so that the logins belonging to multiple server roles will be listed in one row rather than separate rows for server roles. For example, for demonstration purposes, I have added the TestLogin to both the sysadmin and the setupadmin server roles. Without added formatting, these two server roles will show in individual rows for the TestLogin. Then, I formatted the report so that the TestLogin will have both the sysadmin and setupadmin server roles listed in one row rather than the previous individual row for each. Here’s the screen-shot of the explanation and end result:
Formatting the Server Roles into a Row

The correlated subquery and the STUFF() function did all the magic. Here we go with the script:

-- find all logins and their server role memberships
IF OBJECT_ID('tempdb..#Logins_SrvRoles') IS NOT NULL
	DROP TABLE #Logins_SrvRoles;	

SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
INTO #Logins_SrvRoles
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

-- formatting the server roles for the logins into a row
SELECT L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database],
	STUFF((SELECT '; ' + LR.[Server Role]
		FROM #Logins_SrvRoles LR
		WHERE LR.[Login Name] = L.[Login Name]
		FOR XML PATH('')),1,1,''
	) [SRV_Roles]
FROM #Logins_SrvRoles L
GROUP BY L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database]

How to find all logins and their server role memberships?

Have you ever wished to know or have you ever been requested to create a report to show what logins belong to what server roles? While sys.server_role_members alone can produce the logins and server roles by supplementing it using the system function SUSER_NAME() as in:

SELECT SUSER_NAME(sr.role_principal_id) AS [Server Role]
		, SUSER_NAME(sr.member_principal_id) AS [Login Name]
	FROM sys.server_role_members sr

If you need to see other user data columns such as is_disabled, create_date, modify_date, and default_database_name of the logins, you will need to join sys.server_role_members to sys.server_principals. The following is one way that this can be done:

-- find all logins and their server role memberships
SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')

How to find all database users and their role membership?

You need to generate security reports to retrieve the database users and their role memberships for SOX or other auditing reports requirements or for security troubleshooting purposes. SQL Server has a number of system tables when joined together can help you produce the security report needed. These tables are:

  • sys.sysusers
  • sys.sysmembers
  • master.dbo.syslogins

While these system table are considered deprecated, they are still being supported on SQL Server 2012 for backward compatibility. I will be working on an updated version when time permits. In case you have your version of the script using the updated versions of the system tables and views, please share. Updated version is now available using the sys.database_role_members and sys.database_principals system tables at the bottom of this blog.

Note that I have specifically not using the undocumented and unsupported stored procedure SP_MSFOREACHDB as it misses databases. A simple WHILE loop can accomplish the same.
Here we go with the script:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	login_name nvarchar(123),
	default_db nvarchar(123)
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME() AS [Database Name],
				su.name AS [Database User],
				CASE 
					WHEN (ug.uid is null) THEN ''public''
					ELSE ug.name
				END AS [Database Role],
				CASE 
					WHEN su.isntuser=1 then ''Windows Domain Account''
					WHEN su.isntgroup = 1 then ''Windows Group''
					WHEN su.issqluser = 1 then ''SQL Account''
					WHEN su.issqlrole = 1 then ''SQL Role''
				END AS [Account Type],
				l.loginname AS [Login],
				l.dbname AS [Default DB]
			FROM
			   sys.sysusers su LEFT OUTER JOIN
			   (sys.sysmembers sm INNER JOIN sys.sysusers ug ON sm.groupuid = ug.uid) ON su.uid = sm.memberuid
				LEFT OUTER JOIN master.dbo.syslogins l on su.sid = l.sid
			WHERE
			   (su.islogin = 1 and su.hasdbaccess = 1) and
			   (ug.issqlrole = 1 or ug.uid is null)';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.login_name AS [Login Name],
	dsr.default_db AS [Deafult Database]
FROM #dbs_users_roles AS dsr;

Updated on October 05, 2014 to use the sys.database_role_members and sys.database_principals system tables:

-- find all database users and their role memberships
DECLARE @dbname nvarchar(123)
, @id int
, @max int
, @cmdDBUsersRoles nvarchar(max)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
    DROP TABLE #db_list

CREATE TABLE #db_list
(
    id int identity (1,1)
    , dbname nvarchar(123)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_users_roles') IS NOT NULL
	DROP TABLE #dbs_users_roles

CREATE TABLE #dbs_users_roles
(
	dbname nvarchar(123),
	dbuser nvarchar(123),
	dbrole nvarchar(123),
	account_type nvarchar(123),
	create_date datetime,
	modify_date datetime
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0;

-- initialize the counters 
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process 
WHILE (@id <= @max)
BEGIN
    SELECT @dbname = dbname
    FROM #db_list
    WHERE id = @id;

	SET @cmdDBUsersRoles = 'USE ' +@dbname+
		' SELECT DB_NAME(), 
			dp.name,
			rp.name,
			dp.type_desc,
			dp.create_date,
			dp.modify_date
		FROM sys.database_role_members drm JOIN sys.database_principals rp
				ON drm.role_principal_id = rp.principal_id
			JOIN sys.database_principals dp
				ON drm.member_principal_id = dp.principal_id';

	INSERT INTO #dbs_users_roles
	EXEC (@cmdDBUsersRoles);

	SET @id = @id + 1;
END


SELECT dsr.dbname AS [Database Name],
	dsr.dbuser AS [Database User],
	dsr.dbrole AS [Database Role],
	dsr.account_type AS [Account Type],
	dsr.create_date,
	dsr.modify_date
FROM #dbs_users_roles AS dsr;

How to resolve the users “disabled” issue?

First of all, let’s clarify this up front! There’s no such thing as disabling and enabling users in SQL Server. If you keep on communicating to other professional SQL Server DBAs that you wish to disable or enable a user, we will put you in the “ESPN C’MON MAN!” segment because you had fumbled in the 1 yard line without anyone tackling you. Make no mistake about it. There’s disabling and enabling logins. But, there’s no such thing as disabling and enabling users! What if you see the user has the “Red Down” arrow? Do not Google too much on this topic or else some of the forums will tell you to remove the user and then create it again! Don’t do it. The following will describe the symptom and the remediation.

If you see the user with the “Red Down” arrow like the following screen-shot, the most likely cause is that someone had revoked the “CONNECT” privilege from the user or someone had just performed the following steps: 1) created a login from Windows, 2) created the user in the corresponding database, 3) then added the user to a database role. In both cases, the “CONNECT” privilege is missing.
User CONNECT Permission Revoked

To reproduce this, you can use the following steps:
1. Create the test login.

-- Create Login
USE [master];
CREATE LOGIN [Billy] WITH PASSWORD=N'Bob', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; -- this is for demo only

2. Create the test user.

USE [AdventureWorks2012];
CREATE USER [Billy];

3. Add the user to a database role. Note: for SQL Server Logins, you will not experience the “CONNECT” not granted issue as in the case for Windows Logins.

USE [AdventureWorks2012];
EXEC SP_ADDROLEMEMBER 'db_datareader', 'Billy'

4. Revoke the “CONNECT” privilege from the user. Note: you need to use [] around the user as in [Billy]. You will then see the user “disabled” with the “Red Down” arrow next to it.

USE [AdventureWorks2012];
REVOKE CONNECT FROM [Billy];

Solution:
To resolve this issue, you will just need to grant the “CONNECT” privilege to the user. Yes. That’s all she wrote!

USE [AdventureWorks2012];
GRANT CONNECT TO [Billy];

To confirm the user has access:

-- To confirm the user has access to the database
USE [AdventureWorks2012];
SELECT name, 
	hasdbaccess
FROM sys.sysusers 
WHERE name = 'Billy'