Procedure error/ security issue

Question

Post reply

Procedure error/ security issue

Tara-1044200

SSCoach

Points: 15951
More actions
October 11, 2010 at 6:17 pm

#230236

Here is the procedure i have created so that any one who has access to exec this proc should be able to run the code embeded in it. But i get hte error as below which i guess is due to security issues. How could i achieve this successfully?
ALTER PROCEDURE [dbo].[usp_denytab]
@DATABASENAME varchar (100)
WITH EXECUTE AS owner
AS
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX)
SELECT @sql= 'use '+@DATABASENAME+' ;
Declare @TableName varchar(128),@count int,@UserName varchar(128)
set @count = (select count(name) from sys.tables
where name in (''empssn'', ''emppvt'')
print @Count
if @count = 0
begin
Print ''No Tables found ''
end
else
begin
declare @sql nvarchar(1000)
declare curTbl cursor for
select t.Name, p.name from sys.tables t cross join (select name from sys.database_principals
where name not in (''state.az\mlevan'', ''dbo'')
and type in (''G'', ''U'')) p
where t.name in (''empssn'', ''emppvt'')
open curTbl
fetch next from curTbl into @TableName, @UserName
While @@Fetch_Status = 0
BEGIN
print @TableName
print @username
set @sql =
''DENY ALTER ON [dbo].['' + @TableName + ''] TO ['' + @UserName +''];
DENY DELETE ON [dbo].['' + @TableName + ''] TO ['' + @UserName +''];''
exec (@SQL)
fetch next from curTbl into @TableName, @UserName
END
close curTbl
deallocate curTbl
end
'
exec (@SQL)
Error Returned...
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'empssn', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 2
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'emppvt', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 2

Viewing 5 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

What does this return?

SELECT schema_name(schema_id), name

FROM sys.tables

WHERE name = 'empssn'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

Here is the result

dboempssn

this proc runs ok when a sysadmin or security admin executes it but not others which is where iam trying to get.

WayneS SSC Guru Points: 95461 More actions · Answer 3

Tara-1044200 (10/11/2010)
this proc runs ok when a sysadmin or security admin executes it but not others which is where iam trying to get.

Then change "with execute as owner" to "with execute as dbo"

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

NO, it doesnt work that way. I still get the error below

The server principal "sa" is not able to access the database "Dev_revenue" under the current security context.