Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

server role and permission Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 11:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
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?
Post #1533295
Posted Tuesday, January 21, 2014 1:47 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
Any help, please?

Thanks
Post #1533339
Posted Tuesday, January 21, 2014 2:30 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,992, Visits: 12,941
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.


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

Post #1533360
Posted Tuesday, January 21, 2014 2:40 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1533367
Posted Tuesday, January 21, 2014 3:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 803, Visits: 718
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
Post #1533382
Posted Tuesday, January 21, 2014 3:57 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1533395
Posted Tuesday, January 21, 2014 4:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 803, Visits: 718
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
Post #1533399
Posted Tuesday, January 21, 2014 4:16 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,992, Visits: 12,941
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.


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

Post #1533402
Posted Tuesday, January 21, 2014 4:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1533403
Posted Tuesday, January 21, 2014 4:25 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 1,776, Visits: 3,222
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
Post #1533404
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse