Permission

Question

Permission

SSCarpal Tunnel

Points: 4177

November 29, 2004 at 9:43 am

I want to give my developer access to one of the store Procedure, the store procedure has an update and insert activity.

when I give him execute permission on that store procedure I am getting Error you don't have privileges on Insert and update.

Its SQL Authentication, is any way where I can avoid giving him access on tables for insert and update.

Thanks

Nita

Viewing 9 posts - 1 through 8 (of 8 total)

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

You could create a view of the table that you do not want him to have access to and then change the grant inserts / updates to the view and change the sp to use the view.

A.J.
DBA with an attitude

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Just make sure the stored procedure and table are both owned by dbo. Then you can grant EXECUTE for the stored procedure to a user without granting table privilages.

Greg

iLoveSQL SSCrazy Points: 2888 More actions · Answer 3

Anyway, please correct me if I am wrong.

If you need to insert on other database and have sp3a installed, either you have to enable "Allow corss-database ownership chaining" or give the user permission to be able to hit that database.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

Yikers! Don't turn on Allow Cross Database ownership chaining! that thing has nasty security implications. In anycase, it only has an affect you are accessing something in "databaseA" from "databaseB", hence cross database.

If you want your developer to be able to execute a stored procedure, grant execute access on the procedure. If you have explicitly set DENY permissions on your table, you may have some issues.

If your developer is not part of any roles in the database, and there are no 'public' permissions on the table, then your sproc should allow him to make the required changes.

Remember with security: less is more. A weird concept, but the less permissions you GRANT or DENY the safer you are. This makes sense. If you never grant any permissions someone shouldn't be able to access something. Unless you did something bad like made them db_owner, gave them a database/server role, or declared public permissions.

Julian Kuiters
juliankuiters.id.au

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 5

Maybe a silly question, but how do define "declaring public permissions"? When you create a database and add tables, sps, views, etc to it, then you go to the permissions window for public, nothing is checked nor unchecked. Does this mean that public has permissions or doesn't have permissions?

I want to do a similar thing on my database. I want to set up a role that has limited access to only the views in my database. When this developer logs into Query Analyzer, I don't want him to see all of the tables and sps. I only want him to see the views. But I can't figure out how to make this happen.

Any help would be appreciated!

Jana

J. Bagwell

UVA Health System

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 6

"Public" permissions are the same as declaring permissions for everyone in the database. When you create a table, no permissions are declared to begin with, you add them as needed. When Public has nothing checked or crossed out, Public has no permissions, and only people who are in the db_owner, db_datareader or db_writer roles should be able to select/insert/update/delete against the table. (Anyone in the sysadmin can of course do whatever they like).

In every database enviornment, public should have no permissions. No GRANT. No EXECUTE. No DENY. Permissions should be set for individual logins or roles only. This ensures that only the permissions you specifically set for each login/role is used, and not some default.

Query Analyzer does some tricky stuff to show all those tables and views in the Object Browser. However, just because a developer can see them, doesn't mean they can use them. If your permissions are setup correctly, QA will only show the databases the user has access to, but all objects in the database. If the developer trys to select somethign from a table they don't have permission to, they should get a X permission DENIED on object X message. If they don't your permissions are not setup correctly.

If you have a development / playground environment, create a user and try out various permissions to see what happens.

Julian Kuiters
juliankuiters.id.au

J Bagwell Mr or Mrs. 500 Points: 514 More actions · Answer 7

Thanks Julian, that helps a lot.

I've gotten it to the point where the user would get the permission denied error if they tried to open a table in QA or if they tried to run a script against the database in QA, but I'm still troubled by the fact that they can not only see the tables, but they can right click and create a script for the table or stored procedure or whatever.

I know that they can't run that script on my database, and maybe I'm just being a control freak, but I don't want them to get bogged down in how the tables and procedures are designed and coded. And I don't want them to be able to recreate my database on their own server.

Is there any way to limit the user's ability to see this stuff in QA?

Thanks.

J. Bagwell

UVA Health System

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 8

Not that I know of. You could change the Object Owner to being someting other dbo, and then only the new owner would be able to see the objects. But that is just going to cause you a whole lot of trouble.

The reason QA can see the objects even though the person has no permissions is because the object browser uses the sysobjects table, and doesn't attempt to interpret a users permissions on the objects.

Julian Kuiters
juliankuiters.id.au