Hey, folks!
Have a problems with subject. Here is my stor proc. When I'm trying to run this one as Encrypt user (he has db_securityadmin role in all databases), it says "You can only grant or revoke permissions on objects in the current database." If I'm changing userId on SA (db_owner), result the same. How to avoid it?

--------------------------
CREATE PROCEDURE Encrypt.GrantsForEncrypt @GrantRevoke tinyint, @ServerName varchar(50), @DBName varchar(50), @TableName varchar(50), @UserId varchar(50) AS
declare @sqlGrant varchar(200)
set @sqlGrant1 = 'GRANT SELECT, UPDATE ON ' + @DBName + '.dbo.[' + @TableName + '] TO [' + @UserId + ']'
if @GrantRevoke = 0
begin
     exec (@sqlGrant)
end
----------------------------

and one more question... how to grant permissions on object in the other database servers? when I'm trying to modify my stor proc with follow code

set @sqlGrant1 = 'GRANT SELECT, UPDATE ON [' + @ServerName + '].' + @DBName + '.dbo.' + @TableName + ' TO [' + @UserId + ']'

it says "The object name 'reports.db.foo.com.test.dbo.' contains more than the maximum number of prefixes. The maximum is 2." where reports.db.foo.com is name of the Database server.
Is there any solutions about these two problems??? I need help ASAP.
Thank you.

read

set @sqlGrant = 'GRANT SELECT, UPDATE ON ' + @DBName + '.dbo.[' + @TableName + '] TO [' + @UserId + ']'

instead of

set @sqlGrant1 = 'GRANT SELECT, UPDATE ON ' + @DBName + '.dbo.[' + @TableName + '] TO [' + @UserId + ']'

Per SQL BOL "GRANT" "Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database."
EXEC ('Use pubs GRANT SELECT ON dbo.authors TO PUBLIC') -- This works to go after other databases

As far as other servers? I have tried OPENQUERY on a linked server and unless the first statement is something that returns a result set it will give a syntax error.
So while I can get the follow statement to execute the GRANT does not actually take effect (even though I can see the Grant statement in profiler when I trace by statement and Object GDR event)
SELECT * FROM OPENQUERY(TEST,'SELECT ''x'' as Testme Use pubs GRANT SELECT ON dbo.authors TO PUBLIC')


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thanks a lot David! Snippet with another DB works properly, but I still can't grant permissions on objects on another servers :( I tryed OpenQuery before, but it didn't help me. OpenQuery can see only first statement, that's not good :( Can anybody help me solve this problem, please. I still need it.
Thank you.

After reading an article http://www.sommarskog.se/share_data.html by Erland Sommarskog, SQL Server MVP, Ifound the answer -- OpenQuery issues a SET IMPLICIT_TRANSACTIONS ON statement and so upon disconnect it rollsback any actions. I had suspected something like this!

So if you Commit the Transaction and then open a new one do the following you can get the OpenQuery to work
--Uses local server just for example -- can use on other linked servers
DECLARE @LSExists int
EXEC @LSExists = sp_helpserver @server = 'LOCALSERVER'

IF @LSExists = 1
BEGIN
    EXEC sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
                        @provider = 'SQLOLEDB', @datasrc = @@servername
END

Use Pubs
-- setup test scenario
DENY SELECT ON authors to PUBLIC

--Verify that
EXEC sp_helprotect @name = 'authors'
     , @username = 'PUBLIC'

SELECT * FROM OPENQUERY(LOCALSERVER,'SELECT ''x'' as t Use pubs GRANT SELECT ON dbo.authors TO PUBLIC commit tran')

EXEC sp_helprotect @name = 'authors'
     , @username = 'PUBLIC'

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com