SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


server role and permission


server role and permission

Author
Message
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
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?
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
Any help, please?

Thanks
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23976 Visits: 13698
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.

---------------------------------------------------------------------
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
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
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5062 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
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.


[b]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
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5062 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23976 Visits: 13698
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.

---------------------------------------------------------------------
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
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

sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10067 Visits: 4195
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search