deny permission - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

deny permission

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-05-13 : 00:39:35

Hi Team,

I want to DENY the EXECUTE AS for a login. How to do that. Can anybody share their views??

Here is what i have done.

use master
go
create database testdb

go

-- creating a login

USE [master]

GO

CREATE LOGIN [login1]
WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

GO

USE testdb

GO

CREATE USER [login1] FOR LOGIN [login1]

GO

USE testdb
GO

EXEC sp_addrolemember N'db_owner', N'login1'

GO

-- create one more login with only "public" role

USE [master]

GO

CREATE LOGIN [login2]
WITH PASSWORD=N'test',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

GO

USE testdb

GO

CREATE USER [login2] FOR LOGIN [login2]

GO

-- as "login1" created a stored procedure and granted executed the EXECUTE permission to "login2"

created PROCEDURE dbo.test_proc
(
@p1 varchar(max)
)
WITH EXECUTE AS 'login1'
AS
BEGIN
select SYSTEM_USER
SELECT @@SERVERNAME
END

go

-- grant execute permission to 'login2'

GRANT EXECUTE on dbo.usp_test to login2;

-- as login1
exec dbo.test_proc 'dsgf'
/* working fine */

-- as login2
exec dbo.test_proc 'dsgf'
/* working fine */

-- now to restrict the login2 to be impersonated as 'login1' done the following

-- server level

USE master;

DENY IMPERSONATE ON LOGIN::login1 TO login2;

GO

-- database level

USE testdb;

go

DENY IMPERSONATE ON USER::login1 TO login2;
GO

-- now tested as login2
exec dbo.test_proc 'dsgf'
/* working fine */

But i dont want this behaviour to happen.... whenever 'login2' is trying to impersonate as 'login1', we should allow to do that. that's why am denying at server level and database level.

IF i remove the EXECUTE permission, am able acheive what i need.
But basically, if 'login2' is being denied at server level and database level, why it is not restricting the esxectin of the stored procedre.

Now first I want understand what implicit permission's will be granted to a user who is having the EXECUTE permissions on a stored procedure.????????????

I tried modifying the stored procedure, even it is able to execute bt i dont want that to happen.

created PROCEDURE dbo.test_proc
(
@p1 varchar(max)
)
WITH EXECUTE AS CALLER;
AS
BEGIN
select SYSTEM_USER
SELECT @@SERVERNAME
END

go

But if i say one of the below then it is working as i expected.

use testdb

go

EXECUTE AS LOGIN = 'login1'

SELECT SYSTEM_USER

REVERT

SELECT SYSTEM_USER

go

/*

Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
*/

Similarly,
use testdb

go

EXECUTE AS USER = 'login1'

SELECT USER_NAME()

REVERT

SELECT USER_NAME()

go

/*
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "login1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
*/

This is what am expecting to behave whenever executing the stored procedure as well.

-- as 'login2'
use testdb
go
exec dbo.test_proc 'dsgf'
go

I want to know what implicit permissions will a login/dbuser gets if he has EXECUTE permission on a stored procedre????

Can anyone answer this???

Thanks in advance.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-13 : 17:21:03

In order to use EXECUTE AS you need to be able to IMPERSONATE the target User. I have not tested this but would the "DENY IMPERSONATE TO [MyUser];" turn the trick?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2011-05-13 : 22:53:26

I have tried that bt didnt work.

I have denied the EXECTE permission on stored procedres.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources