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»»

Grant persmissions to database Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 12:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 1,720, Visits: 3,063
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

Post #1430561
Posted Wednesday, March 13, 2013 12:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 1,783, Visits: 1,920
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
Post #1430566
Posted Wednesday, March 13, 2013 12:25 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 604, Visits: 477
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.

.
Post #1430569
Posted Wednesday, March 13, 2013 1:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 1,720, Visits: 3,063
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
Post #1430595
Posted Wednesday, March 13, 2013 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 1,783, Visits: 1,920
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
Post #1430611
Posted Wednesday, March 13, 2013 1:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 1,720, Visits: 3,063
Thanks much.
Post #1430614
Posted Wednesday, March 13, 2013 9:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
+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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1430726
Posted Wednesday, March 13, 2013 9:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 14, 2013 8:05 AM
Points: 101, Visits: 108
Assign db_executor, db_datareader, db_datawriter roles to user.
Post #1430730
Posted Wednesday, March 13, 2013 10:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1430747
Posted Thursday, March 14, 2013 11:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 1,720, Visits: 3,063
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
Post #1431152
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse