September 28, 2004 at 4:43 pm
Hi everyone,
I have a problem. I need to make sure that user X cannot write to a particular database, but at the same time be able to run a stored procedure that inserts data into that database. Is this possible? Thanks in advance for your help.
September 28, 2004 at 6:30 pm
Yes. Easily. Table permissions are entirely different than EXECUTE permission on stored procedures. Simply don't grant any INSERT, UPDATE, or DELETE authority to any tables in the database and then grant EXECUTE authority to the stored procedure.
September 28, 2004 at 6:59 pm
Hi Aaron,
The user has execute permission on the stored procedure, but when the stored procedure tries to insert, he gets a permission error.
September 28, 2004 at 8:29 pm
Is it dynamic SQL? Dynamic SQL runs under the individual user's authority, not the SP (it is treated as a completely seperate batch). If so, see what you can do the get rid of the dynamic SQL. If you can't think of anything post it and we may be able to help.
I'm not real solid on ownership relationships, but I suppose it could be an issue where the table is owned by someone other than the stored procedure and therefore implicite update authority is not granted in that case. If so, perhaps changing ownership is an option, at least to test the theory. I'm kind of guessing here, though.
September 29, 2004 at 10:04 am
Hi Aaron, thank you for your thoughts. The code is actually compiled as a stored procedure and the user has execute permissions on the SP. His code inserts data into database X. He is a member of Public, dp_datareader, and dp_denydatawriter. When he executes the SP, he gets a permissions error. When I remove him from dp_denydatawriter and give him dp_datawriter role, the SP runns successfuly. I am at a loss on what to do. Again, thanks for your help.
September 29, 2004 at 11:05 am
Ahhhhhhhhh.
Denying DataWriter is different than not granting DataWriter. The former will override the implicit SP rights. Simply grant datareader (only if the user needs read access outside of SPs) and don't grant or deny DataWriter.
September 29, 2004 at 4:11 pm
Hi Aaron,
I tried what you said and had the user run the stored procedure and he got the same access violation error saying that he cannot insert into a table.
Just to recap, the user is a member of Public and db_datareader only and he was removed from the db_denydatawriter role. He has execute permission on the SP.
Thanks for all of your help!
September 29, 2004 at 4:18 pm
hmm..
Is he using SQL login or Windows login? Does the insert happening on only one database or cross database?
September 30, 2004 at 5:00 am
The "owner" of the stored procedure needs permission to write to the tables in database 2. Who owns the sproc? When a procedure is executed it is run with the permissions of the owner of the procedure.
September 30, 2004 at 10:56 am
Hi everyone,
I posted a response and just checked and the response is not posted for some reason.
He is using Windows login. The SP has two insert statements, one is to database X on server 1 and other is to database Y on server 2. The insert to database X succeeds, and the other fails. Also, JFYI, database Y is replicated from database X (on a nightly basis) using an EMC software. Once the database replication is over, I run a script that grants database access and permissions to all users. Not sure if this has anythig to do with the problem though.
The SP is owned by dbo. I think that the SP is not run under dbo entirely. I just created a table and a SP that inserts data into that table. I gave execute permissions to my user and another user and had both of them execute the SP. The one that is having problems with the other SP had the same error while the other person did not have a problem.
Thanks again for your help everyone.
September 30, 2004 at 12:42 pm
Hi,
I am not getting what is it about 2 servers. Do you have a second server linked? Did you configure linked server logins correctly? Who is the DBO on the database on the secod server? I used to move databases for developers and the common problem is when a source database is created by a Windows login and a DBO is this Windows login, not SA, then when the database is moved then DBO is nobody if you look in the database users container in Enterprise Manager on the second server. I fix it by running
sp_changedbowner 'sa'
then dbo is mapped to sa and they are fine.
If the databases are on the same server, check if "Allow cross-database ownership chaining" is checked.
Also check if the problem user is a member of another role or Windows group that is denied permissions. Like you even may not know this users belongs to another global group that is denied permissions as SQL Server role
regards,
Yelena
Regards,Yelena Varsha
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply