September 17, 2010 at 12:58 pm
I have create stored proc where in it will DENY permissions for a user to insert/update a table but only sysadmin(may be security admin too) can run that proc but not others. I want that stored proc to be used by a non admin by using " EXECUTE AS" but its not working, any other alternative?
September 17, 2010 at 2:48 pm
One Alternative ...
You can Grant Execute Privileges on that Proc to that non sysadmin user so that he can execute it.
Thank You,
Best Regards,
SQLBuddy
September 17, 2010 at 6:48 pm
I am not sure what you are trying to say but though i give him permissions to exec jus the procedure that would not be sufficiant because inside the proc i am using DENY INSERT,UPDATE which requires either sysadmin or security admin role on the server.
September 19, 2010 at 7:36 am
any idea how this can be done?
September 20, 2010 at 2:13 am
Can you give more info about why it's not working?
With execute as you should be able to do what you want while just granting execute on the procedure you created.
September 20, 2010 at 4:22 am
Tara-1044200 (9/17/2010)
i am using DENY INSERT,UPDATE which requires either sysadmin or security admin role on the server.
INSERT/UPDATE in stored proc ??? confusing stuff for me :unsure:. my guess is you only want that user can only execute the Sp. no ALTER or MODIFICATION of stored proc, if yes the use this GRANT EXECUTE on SP to user(login)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 20, 2010 at 7:06 am
I think you got me wrong, user already has permissions to INSERT and UPDATE but i want the user to execute the stored proc by which the user will be able to DENY INSERT,DENY UPDATE on for other users of the database. hope its clear.
September 20, 2010 at 7:26 am
Tara-1044200 (9/20/2010)
I think you got me wrong, user already has permissions to INSERT and UPDATE but i want the user to execute the stored proc by which the user will be able to DENY INSERT,DENY UPDATE on for other users of the database. hope its clear.
I've just tried it and it's working fine using EXECUTE AS.
If it's not working for you then tell us what went wrong (error message or stuff like that).
Maybe describe a bit more what you tried as well.
September 28, 2010 at 3:42 pm
OK i have created the stored proc by using " Execute as owner' but getting the following error message
Msg 916, Level 14, State 1, Line 1
The server principal "sa" is not able to access the database "Revenue_Employee_ns" under the current security context.
September 29, 2010 at 7:23 am
bhuvanesh, thgough you give exec permission to the proc it will not allow to do security admin stuff unless you havethat previlage.
October 5, 2010 at 9:35 am
any idea on how to give previalges to a user so that user can deny permissions for others on a table through a stored proc?
is there a way to impersonate security admin previalges just for that stored proc?
October 11, 2010 at 5:55 pm
Can someone please tell me if this is really possible or not ?
October 12, 2010 at 4:40 pm
Use "with execute as 'dbo' "
------------------------------------------------------------------------------------------------------------------
-- Prep
------------------------------------------------------------------------------------------------------------------
-- Create test users and test db
USE [master]
GO
CREATE LOGIN [testAdmin] WITH PASSWORD=N'Dork', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE LOGIN [testUser] WITH PASSWORD=N'Dork', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE DATABASE [testDB]
GO
-- Add test users to test db. Give testAdmin dbo.
USE [testDB]
GO
CREATE USER [testAdmin] FOR LOGIN [testAdmin]
GO
EXEC sp_addrolemember N'db_owner', N'testAdmin'
GO
CREATE USER [testUser] FOR LOGIN [testUser]
GO
-- Create test table, data, grants.
CREATE table dbo.myTable (a int)
GO
insert dbo.myTable select 1
insert dbo.myTable select 2
GO
grant select,insert,update,delete on dbo.myTable to [testUser]
GO
-- Create SP that runs some elevated privs.
create proc [spRunSomePrivAction]
with execute as 'dbo'
as
deny select,insert,update,delete on dbo.myTable to [testAdmin]
GO
-- Grant SP to lowly testUser.
grant execute on dbo.spRunSomePrivAction to [testUser]
GO
------------------------------------------------------------------------------------------------------------------
-- Test
------------------------------------------------------------------------------------------------------------------
-- 1. Run as testAdmin (see how he can select from tbl)
C:\>sqlcmd -S shuttle1 -d testdb -U testadmin -P Dork
1> select * from mytable
2> go
a
-----------
1
2
-- 2. Run as testUser (run the SP to deny testAdmin)
C:\>sqlcmd -S localhost -d testdb -U testuser -P Dork
1> exec spRunSomePrivAction
2> go
1>
-- 3. Run as testAdmin again (see the denial).
1> select * from mytable
2> go
Msg 229, Level 14, State 5, Server SHUTTLE1, Line 1
The SELECT permission was denied on the object 'myTable', database 'testDB', schema 'dbo'.
1>
------------------------------------------------------------------------------------------------------------------
-- Cleanup
------------------------------------------------------------------------------------------------------------------
use master
GO
DROP LOGIN [testAdmin]
GO
DROP LOGIN [testUser]
GO
DROP DATABASE [testDB]
GO
October 12, 2010 at 4:47 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply