permission script

Question

permission script

sqlfriend

SSC Guru

Points: 52794
More actions
January 3, 2014 at 1:33 pm

#284095

We need to add a user to our server for a vendor network monitoring tool.
The provide in their documentation of a script that creates the permissions.
It is in the attachment of this post.
But I don't quite understand the script, esp, the last line, does it mean to add the user to each database on the server, or does it mean to only add the user to msdb database?
If it is added to each database, what permission it grants to?
Thanks,

Viewing 10 posts - 1 through 10 (of 10 total)

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

homebrew01 SSC Guru Points: 55553 More actions · Answer 1

sp_MSforeachdb will run the command/script in every database.

I use it when I want to search every database for something, such as a "where used" scenario, but would be hesitant to make changes to all databases that way.

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks, it adds it to each database, but didn't grant any permission,

But I guess it defaults to connect permission, that is just what the vendor product needs

Perry Whittle SSC Guru Points: 234064 More actions · Answer 3

sqlfriends (1/7/2014)
Thanks, it adds it to each database, but didn't grant any permission,
But I guess it defaults to connect permission, that is just what the vendor product needs

It will default to public role

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

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

So does it have connect permissions?

If not, how this works, the vendor's script grant the service account to each database without specifically add any other permissions, but they said this is for to grant connect to each database.

I am not getting it.

See http://knowledgebase.solarwinds.com/kb/questions/4851/AppInsight+for+SQL+Requirements+and+Permissions

Perry Whittle SSC Guru Points: 234064 More actions · Answer 5

yes it will have connect permission, it will also default to the public database role for access

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

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

Thanks, I cannot find any information on line about Public role has connect permission to a database.

Could you provide a link that tells public role has connect permission.

What I understand is everyone is a member of public role, so why this vendor script add this account to each database?

Thanks,

Lowell SSC Guru Points: 323546 More actions · Answer 7

yeah, i didn't see a value in it either;

the grant for GRANT VIEW ANY DEFINITION TO AppInsightUser

lets the login see the metadata without having to be a user int he database anyway;

unless there is another command that is granting that user db_datareader in each db as well,, i don't see the need for a user in each db.

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!

Perry Whittle SSC Guru Points: 234064 More actions · Answer 8

sqlfriends (1/8/2014)
Thanks, I cannot find any information on line about Public role has connect permission to a database.
Could you provide a link that tells public role has connect permission.

That's not what I said. Once a user is created in the database and has connect ability they will automatically default to public database role access.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

sqlfriend SSC Guru Points: 52794 More actions · Answer 9

Lowell (1/8/2014)
yeah, i didn't see a value in it either;
the grant for GRANT VIEW ANY DEFINITION TO AppInsightUser
lets the login see the metadata without having to be a user int he database anyway;
unless there is another command that is granting that user db_datareader in each db as well,, i don't see the need for a user in each db.

I totally agree, That's the reason I posted this message. Maybe GRANT VIEW ANY DEFINITION is not enough?

Then it grant the user to the database.

But what does it really do , I don't know.

Thanks