server role and permission

Question

Post reply

server role and permission

sqlfriend

SSC Guru

Points: 52769
More actions
January 21, 2014 at 11:33 am

#303140

Hi, I have a vendor network monitoring tool that asks about to create an account on the monitored machine, and grante below permissions.
http://knowledgebase.solarwinds.com/kb/questions/4851/AppInsight+for+SQL+Requirements+and+Permissions.
But the last line in the script is to grant permission to each database.
I am concerned in the future every time we create a new database, we have to add the monitor account into that database.
I am considering to use a server role instead of such granular permissions mentioned in the article.
Is there a minimum server role I can use to include all the permissions mentioned in the script of the article?

Viewing 14 posts - 1 through 13 (of 13 total)

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

sqlfriend SSC Guru Points: 52769 More actions · Answer 1

sqlfriend

SSC Guru

Points: 52769

January 21, 2014 at 1:47 pm

#1682155

Any help, please?

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 2

well - sysadmin - no only joking, don't do that 🙂

that script will add the user to model as well, so all new databases would automatically get the user as model is the template for new databases.

---------------------------------------------------------------------

sqlfriend SSC Guru Points: 52769 More actions · Answer 3

george sibbald (1/21/2014)
well - sysadmin - no only joking, don't do that 🙂
that script will add the user to model as well, so all new databases would automatically get the user as model is the template for new databases.

Thanks, George. I will definitely not using sysadmin, but how about serveradmin, or processAdmin, by reading their definitions, I am not clear if they include all the permissions mentioned in the vendor script in the linked page in my original post.

Good catch, the last command -EXECUTE sp_MSforeachdb also add permission to new database.

This saves us the steps to add it.

But for this statement, EXECUTE sp_MSforeachdb, I understand it added the account to each database, but it didn't grant any permission to it, what is the usage of this command according to the doc?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

As George says, the script adds the user to model and master as well. And it will probably will produce an error when it attempts to add the user to msdb again. model solves your concern, but I am not sure that I like it.

But there is no other permission lower than CONTROL SERVER that does this on server level - unless you enable the guest user, and that's even worse. The issue is that if you access a database, you must be someone in that database. And beside being your own user, the only two alternatives are dbo and guest.

Beware that since they are oldfashioned and use sp_adduser, the script also creates a schema called AppInsightUser, and AppInsightUser is the owner of that schema.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

Erland Sommarskog (1/21/2014)
As George says, the script adds the user to model and master as well. And it will probably will produce an error when it attempts to add the user to msdb again. model solves your concern, but I am not sure that I like it.
But there is no other permission lower than CONTROL SERVER that does this on server level - unless you enable the guest user, and that's even worse.

Thanks, it looks like there is not a equivalent server role to do that, and I don't want to grant control server and sysadmin to it.

So the best option I guess is still to run this script on each server the product monitors.

The issue is that if you access a database, you must be someone in that database. And beside being your own user, the only two alternatives are dbo and guest.

So the script adds the user to the database, but without granting any permission, can it still access database, what permission it has?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

The user still has VIEW DEFINITION in the database, as it was granted VIEW ANY DEFINITION on server level.

That is, the user can see the definition of all tables, stored procedures etc.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

george sibbald SSC Guru Points: 104210 More actions · Answer 7

it will have whatever rights are granted to public, we can only surmise that is all the app requires.

It has been granted view any definition so will be able to see metadata.

It is best to keep the permissions at the most granular level anyway rather than try and find a higher level role that would give more permissions than required .

as Erland says you a likely going to have to tweak the script anyway to make it work. Creating an unnecessary schema would be a slight performance hit whenever this ID accessed a database.

---------------------------------------------------------------------

sqlfriend SSC Guru Points: 52769 More actions · Answer 8

Erland Sommarskog (1/21/2014)
The user still has VIEW DEFINITION in the database, as it was granted VIEW ANY DEFINITION on server level.
That is, the user can see the definition of all tables, stored procedures etc.

By reading from the definition from microsoft site below, it look like we grant view definition on server level, also add the user to the database without granting any permissions is equivalent as granting the user the view definition to the securables.

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata,

You can grant VIEW DEFINITION permission to a user on a securable, for example a table. This lets the user see metadata of the table and any subcomponents that are related to the table, such as triggers, constraints, and indexes

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

george sibbald (1/21/2014)
it will have whatever rights are granted to public, we can only surmise that is all the app requires.
as Erland says you a likely going to have to tweak the script anyway to make it work. Creating an unnecessary schema would be a slight performance hit whenever this ID accessed a database.

what is the alternative way to replace sp_adduser to add a user without adding the schema in the EXECUTE sp_MSforeachdb?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

sqlfriends (1/21/2014)
By reading from the definition from microsoft site below, it look like we grant view definition on server level, also add the user to the database without granting any permissions is equivalent as granting the user the view definition to the securables.

No. Try this (replace ssgrep with any database you might have):

USE master

go

CREATE LOGIN bludder WITH PASSWORD = 'offentligt'

GRANT VIEW ANY DEFINITION TO bludder

go

EXECUTE AS LOGIN = 'bludder'

go

SELECT * FROM ssgrep.sys.tables

go

REVERT

go

DROP LOGIN bludder

This produces the error message

Server: Msg 916, Level 14, State 1, Line 1

The server principal "bludder" is not able to access the database "ssgrep" under the current security context.

The example also shows how you easily can test a certain permission scenario easily.

To create a user without creating a schema, use CREATE USER.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

sqlfriend SSC Guru Points: 52769 More actions · Answer 11

Thanks, tried the way you test, it looks like if the user is added to the database without any other permissions, and it has server role view server definition, it will work.

I will also use create user to replace sp_adduser in the last statment.

Thanks much

sqlfriend SSC Guru Points: 52769 More actions · Answer 12

I rewrite the script as this:

USE master

GRANT VIEW SERVER STATE TO [mydomain\sys$swind]

GRANT VIEW ANY DEFINITION TO [mydomain\sys$swind]

GRANT EXECUTE ON xp_readerrorlog TO [mydomain\sys$swind]

EXECUTE sp_MSforeachdb 'USE [?]; CREATE USER [mydomain\sys$swind] FOR LOGIN [mydomain\sys$swind]'

USE msdb

EXEC sp_addrolemember N'db_datareader', [mydomain\sys$swind]

It works great.

And by replacing sp_adduser with create user, there is no schema created for the user for all the databases except one - master, why in master database the user still own a schema that is the same name as the login name?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 13

sqlfriends (1/22/2014)And by replacing sp_adduser with create user, there is no schema created for the user for all the databases except one - master, why in master database the user still own a schema that is the same name as the login name?

I was not able to repeat this. Maybe this is something the application does already at installation?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]