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


Grant persmissions to database


Grant persmissions to database

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: 10287 Visits: 4195
We have 5 small in-house developed applications whose databases are hosted on a SQL server.

For each application we have an execute account that is used by application to access back end SQL databases

So I create the logins in SQL server. Now come to the users security part.

Shall I simplely grant db_owner for these five accounts for their application databases respectively?

They may need to read and write and also execute stored procedures in the databases. So I think db_owner should be simple and fine.


Thanks
Chris Harshman
Chris Harshman
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: 10833 Visits: 4657
sqlfriends (3/13/2013)
...They may need to read and write and also execute stored procedures in the databases...

Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:
GRANT SELECT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT INSERT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT UPDATE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT DELETE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT EXECUTE ON SCHEMA::[schemaname] TO [user or rolename]


http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1241 Visits: 599
If it's the application account, DBOwner may be more expected depending on how it reacts. does it create tables, delete items, insert, update, drop? I agree with least privilege, though the application may actually need DBOwner.

.
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: 10287 Visits: 4195
Chris Harshman (3/13/2013)
sqlfriends (3/13/2013)
...They may need to read and write and also execute stored procedures in the databases...

Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:
GRANT SELECT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT INSERT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT UPDATE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT DELETE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT EXECUTE ON SCHEMA::[schemaname] TO [user or rolename]


http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx


Thanks all,

when granting execute on schema, what does execute mean?

execute stored procedures,

how about functions and triggers etc?

Thanks
Chris Harshman
Chris Harshman
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: 10833 Visits: 4657
sqlfriends (3/13/2013)...when granting execute on schema, what does execute mean?
execute stored procedures,

how about functions and triggers etc?

Thanks

EXECUTE privilege is for stored procedures and scalar functions
SELECT privilege would be for tables, views, and table valued functions
triggers are events that happen behind the scenes and don't have permissions on them
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: 10287 Visits: 4195
Thanks much.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39296 Visits: 14411
+1 to least privilege. Adding members to db_owner is great for allowing developers access to a development database but beyond that I think it should rarely be handed out, if ever.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Ramasankar Molleti
Ramasankar Molleti
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 203
Assign db_executor, db_datareader, db_datawriter roles to user.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39296 Visits: 14411
sankar276 (3/13/2013)
Assign db_executor, db_datareader, db_datawriter roles to user.

db_executor?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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: 10287 Visits: 4195
Chris Harshman (3/13/2013)
sqlfriends (3/13/2013)
...They may need to read and write and also execute stored procedures in the databases...

Typically when it comes to security you want to grant the least privileges that the user needs to do its work. If these 5 users are only doing read, write, and execute type of actions, then DB_OWNER seems excessive. If the databases have schemas then a handy trick is to use permissions at the schema level instead of each individual object, for example:
GRANT SELECT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT INSERT ON SCHEMA::[schemaname] TO [user or rolename]
GRANT UPDATE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT DELETE ON SCHEMA::[schemaname] TO [user or rolename]
GRANT EXECUTE ON SCHEMA::[schemaname] TO [user or rolename]


http://msdn.microsoft.com/en-us/library/ms187940(v=sql.105).aspx



Hi, If I granted permission for above, later if I would like to use a query find out what permission has been granted to this user, is there an easy way to look clearly about it?

Or simply to say how can I query out what permission has been granted to a specific user on a specific database?
Thanks much
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