Search code examples
sqlsql-serverpermissionsimpersonationprocedure

How to grant access to change permissions on all SQL tables?

Please refer the the following SQL Stored Procedure:

ALTER PROCEDURE cspFieldAccess
@Tbl varchar(20),
@Fld varchar(20),
@Usr varchar(35),
@Dny varchar(5),
@All varchar(5)

AS
DECLARE @sql nvarchar(max)
IF @Dny = 'Y' and @All = 'Y' 
set @sql='DENY UPDATE ON ['+@Tbl+'] ('+@Fld+') TO [MCCOSKERS\ViewPoint Production Users]'

ELSE IF @Dny = 'Y' and @All = 'N'
set @sql='DENY UPDATE ON ['+@Tbl+'] ('+@Fld+') TO ['+@Usr+']'

ELSE IF @Dny = 'N' and @All = 'Y' 
set @sql='GRANT UPDATE ON ['+@Tbl+'] ('+@Fld+') TO [MCCOSKERS\ViewPoint Production Users]'

ELSE IF @Dny = 'N' and @All = 'N'
set @sql='GRANT UPDATE ON ['+@Tbl+'] ('+@Fld+') TO ['+@Usr+']'

exec sp_executesql @sql

I can execute this procedure perfectly in SQL (as SA) but I want to get it to work in an application called Viewpoint Construction Software.

Basically, the procedure will allow the user to specify a user, table and field and then deny change access to the field. If the "@ALL parameter is "Y" then it will apply for ALL users.

When I run this from Viewpoint, I get an error message, saying "Cannot find the object 'PREH' ".

FYI : "PREH" is the table that is inputted into Viewpoint.

I am assuming that this error is happening because the user which I am logged in as does not have permission to deny update on PREH. How can I grant this access for to the user ALL tables?

Alternatively, is it possible to execute the procedure as "SA" and then give impersonation rights to the users who I want to have access to do this?

Solution

  • There are a couple of ways to do this.

    1. Grant them CONTROL permission at either the schema or database level. Note that this is for most intents and purposes ownership, and so comes with the ability to alter, drop, etc at the level granted
    2. Sign your procedure with a certificate, create a user from said certificate, grant appropriate permissions (see above) to the certificate user, and finally grant permission on the procedure to whomever you'd like to run it. This has the advantage of letting your users do only things that you've crafted for them without giving away the keys to the castle. But it does come with the cost of complexity.