Read-only security role

Question

Post reply

Read-only security role

lassell

Ten Centuries

Points: 1215
More actions
November 11, 2016 at 12:23 pm

#314877

Hi,
Strange request I know but what server role could I create or use in SQL 2014 that would allow a windows group to view everything a sysadmin could view but ensure they could not action anything?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

interestingproblem;

here's how i think i would tackle it;

substitute [ClarkKent] for your [domain\ADGroup]

Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

--see anything in the DMV's

GRANT VIEW SERVER STATE TO [ClarkKent];

--allow to see object definitions, tables and objects

GRANT VIEW ANY DATABASE TO [ClarkKent];

--allow to see SQL Agent stuff

USE MSDB;

CREATE USER [ClarkKent] FOR LOGIN [ClarkKent];

EXEC [sys].[sp_addrolemember] 'SQLAgentReaderRole','ClarkKent'

--view any data? for each db, you want a to add as a user, and add db_datareader

EXEC sp_msForEachDB '

USE [?];

CREATE USER [ClarkKent] FOR LOGIN [ClarkKent];

EXEC [sys].[sp_addrolemember] ''db_datareader'',''ClarkKent'' ;

'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

Starting with SQL Server 2012, we can create user defined server roles.

For example:

CREATE SERVER ROLE [ReadOnlyAdmin];

Next grant all the necessary permissions and additional role membership to [ReadOnlyAdmin], and then add user logins and/or domain groups to [ReadOnlyAdmin].

http://searchsqlserver.techtarget.com/feature/Create-a-user-defined-server-role-in-SQL-Server-2012-with-T-SQL-SSMS

I've been meaning to create such a role and then add a SQL Server authenticated account for my own use; like when I need to perform power user tasks on a server, stuff like troubleshooting or ad-hoc querying, without risk of logging in as myself with full SYSADMIN membership and accidentally changing something.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

lassell Ten Centuries Points: 1215 More actions · Answer 3

Thanks guys, too simple 🙂 I've moved into a more management role and sadly not kept my basic skills up to date.

Lowell SSC Guru Points: 323518 More actions · Answer 4

Eric thanks for prompting me to investigate Server Roles deeper.

this is the role i came up with, that grants select on any user objects as well as the view server state/definition etc :

SELECT * FROM sys.[server_principals] AS [sp]

IF NOT EXISTS(SELECT * FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE')

CREATE SERVER ROLE [ReadOnlyAdmin]

IF NOT EXISTS(SELECT * FROM sys.[server_permissions] AS [sp] WHERE permission_name = 'CONNECT ANY DATABASE' AND [sp].[grantee_principal_id] IN(SELECT [server_principals].[principal_id] FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE'))

GRANT CONNECT ANY DATABASE TO [ReadOnlyAdmin]

IF NOT EXISTS(SELECT * FROM sys.[server_permissions] AS [sp] WHERE permission_name = 'VIEW ANY DATABASE' AND [sp].[grantee_principal_id] IN(SELECT [server_principals].[principal_id] FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE'))

GRANT VIEW ANY DATABASE TO [ReadOnlyAdmin]

IF NOT EXISTS(SELECT * FROM sys.[server_permissions] AS [sp] WHERE permission_name = 'VIEW ANY DEFINITION' AND [sp].[grantee_principal_id] IN(SELECT [server_principals].[principal_id] FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE'))

GRANT VIEW ANY DEFINITION TO [ReadOnlyAdmin]

IF NOT EXISTS(SELECT * FROM sys.[server_permissions] AS [sp] WHERE permission_name = 'VIEW SERVER STATE' AND [sp].[grantee_principal_id] IN(SELECT [server_principals].[principal_id] FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE'))

GRANT VIEW SERVER STATE TO [ReadOnlyAdmin]

IF NOT EXISTS(SELECT * FROM sys.[server_permissions] AS [sp] WHERE permission_name = 'SELECT ALL USER SECURABLES' AND [sp].[grantee_principal_id] IN(SELECT [server_principals].[principal_id] FROM sys.[server_principals] WHERE name='ReadOnlyAdmin' AND type_desc = 'SERVER_ROLE'))

GRANT SELECT ALL USER SECURABLES TO [ReadOnlyAdmin]

--does the server role need select permissions in each database?

/*

Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

EXEC [sys].[sp_addsrvrolemember] @loginame = [ClarkKent], @rolename = [ReadOnlyAdmin]

EXECUTE AS LOGIN='ClarkKent'

USE SandBox;

GO

--find a table

SELECT * FROM sys.tables

--can i select from the table?

SELECT * FROM ATableThatExistsInSandBox

USE master

REVERT

--DROP LOGIN ClarkKent

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lassell Ten Centuries Points: 1215 More actions · Answer 5

Thanks guys, Sorry for the delay in replying but the country I live in has suffered a few 'shakes' recently and as a consequence I've had an intermittent internet conn.

I live in NZ 😀 a beautiful country but a bit shaky at times.

I've tested it and the solution works great. The only issue I can't solve is access to the SQL error logs. I am assuming this is due to the lack of permissions to extended SP's etc however the idea is for 'read only admins' to view the eventvwr so this may work for us.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

lassell (11/14/2016)
Thanks guys, Sorry for the delay in replying but the country I live in has suffered a few 'shakes' recently and as a consequence I've had an intermittent internet conn.
I live in NZ 😀 a beautiful country but a bit shaky at times.
I've tested it and the solution works great. The only issue I can't solve is access to the SQL error logs. I am assuming this is due to the lack of permissions to extended SP's etc however the idea is for 'read only admins' to view the eventvwr so this may work for us.

You can try the following:

GRANT EXECUTE ON xp_readerrorlog TO ReadOnlyAdmin;

If that doesn't work, then you can perhaps try adding ReadOnlyAdmin role as a member of server role SECURITYADMIN, however, as a side effect this may grant them more permissions than you want to allow, like the ability to manage other logins.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

lassell Ten Centuries Points: 1215 More actions · Answer 7

Good idea. I'll need to check though as most extended SP's are locked down in these environments.

choodye Valued Member Points: 73 More actions · Answer 8

Thanks everyone for your contributions.

The server level roles are a God send, since 2012!

It was a pain to have to write custom scripts for each database for such a small but important task.