Create new user role

Question

Post reply

Create new user role

alvirochka

SSC Rookie

Points: 39
More actions
April 5, 2011 at 1:15 am

#389202

There are several customer databases (CustomDB1, CustomerDB2,...)and one admin (AdminDB) database in the Server. I need to create such the Server/DataBase role, that has permissions to do : select, insert, update, delete, exec SP only on CustomerDB1 and AdminDB, and has no access to CusotmerDB2, CusotmerDB3 and so on.
Help!

Viewing 7 posts - 1 through 6 (of 6 total)

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

forsqlserver SSCoach Points: 18902 More actions · Answer 1

Dear,

Keep in mind start with the minimum privileges.

🙂

Thanks

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 2

Looks to me like you want to allow the user to read and write all data, but not change the structure of the database. Grant the database role of db_datareader and db_datawriter for the databases that you desire them to have this access.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

You also mentioned allowig exec on all SPs...for that you can grant EXEC to a user at the schema or database level but this will also allow said user to execute all user-defined functions as well. If you only want them to execute procedures and not functions you'll have to do that one by one.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 4

As opc.three told, if you required to give permission for each sp, write an sp with UserName, Schema, and Permission parameters. Internally you can get the SPs under the schema and create a dynamic GRANT/DENY query and execute it.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

alvirochka SSC Rookie Points: 39 More actions · Answer 5

In addition to permissions: : select, insert, update, delete, exec SP only on CustomerDB1 and AdminDB, the "limited user" has to has also possibility to "TRUNCATE TABLE"

From http://msdn.microsoft.com/en-us/library/ms177570.aspx I can see that the "limited user" can not do it.

I tried to apply:

CREATE PROCEDURE TruncateMyTable

WITH EXECUTE AS SELF

AS TRUNCATE TABLE

but it returns the error message:

"Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission."

How to grand truncate table permissions without granting owner rights?

Thanks a lot for all your help!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Using EXECUTE AS is the approach I have used but only when using a database user with no associated server login as a proxy. This article from Erland should get you there:

http://sommarskog.se/grantperm.html#EXECUTE_AS

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato