SQL: Test SQLAgentReaderRole role in msdb to allow viewing jobs

\ by

Sometimes a developer or analyst needs to see SQL Agent jobs or its history.

Create a login, add to role, open SSMS and log in as that login which has no other permissions, and view jobs, check history, and notice everything is grayed out.

CREATE LOGIN jperryx WITH PASSWORD = '1anGxxp22'

USE [msdb]
GO
CREATE USER [jperryx] FOR LOGIN [jperryx] 
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [jperryx]
GO

After testing yourself, and knowing what they can do with this access, you can decide if you should grant it or not.

You can find more about it here.

Now clean up.

DROP USER jperryx
GO
DROP LOGIN jperryx
GO
Posted in: Permissions, SQL Server, SQLAgent

SQL ERROR: SSPI handshake failed with error code

\ by 
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.12.23.345].

This can be related to an AD login account expiring and the user leaving SSMS or something running on their PC. The account eventually locks out and SQL sees this message until the user resets their password.

Posted in: ERRORs, Permissions, SQL Server, Windows

SQL – All databases users all their roles, except reader

\ by

For every database return non-db_datareaders, dbo, and ## users database permissions.

-- Declaration of table variable for the storage of results
DECLARE @result AS TABLE (
 database_name SYSNAME,
 role_name SYSNAME,
 principal_name SYSNAME
 );

-- Insert the resultset from each database
INSERT INTO @result
 EXEC sp_msforeachdb N'USE [?]
SELECT CASE WHEN DB_NAME() IN (''master'',''msdb'',''model'',''tempdb'') THEN ''*'' + DB_NAME() ELSE DB_NAME() END AS database_name,
 USER_NAME(role_principal_id) AS [Role],
 USER_NAME(member_principal_id) AS [User]
FROM sys.database_principals p
INNER JOIN sys.database_role_members m
ON (p.principal_id = m.member_principal_id)
INNER JOIN sys.database_principals r
ON (m.role_principal_id = r.principal_id)
WHERE r.name NOT IN (''db_datareader'')
 AND USER_NAME(member_principal_id) <> ''dbo''
 AND USER_NAME(member_principal_id) NOT LIKE ''##%''
ORDER BY [Role],
 [User]
;'
-- Display the result
SELECT *
FROM @result
ORDER BY database_name,
 principal_name;
Posted in: Permissions, SQL