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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3909 Visits: 4025
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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5042 Visits: 4010
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)
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 588
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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3909 Visits: 4025
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
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5042 Visits: 4010
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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3909 Visits: 4025
Thanks much.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15123 Visits: 14396
+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-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 203
Assign db_executor, db_datareader, db_datawriter roles to user.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15123 Visits: 14396
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
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3909 Visits: 4025
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