|
Subject:
|
Grant permissions on object from other DB
|
|
Posted By:
|
Dimon
|
Post Date:
|
12/23/2005 4:47:13 PM
|
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.
|
|
Reply By:
|
Dimon
|
Reply Date:
|
12/23/2005 6:03:39 PM
|
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 + ']'
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
12/27/2005 12:01:05 PM
|
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
|
|
Reply By:
|
Dimon
|
Reply Date:
|
12/28/2005 1:33:58 PM
|
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.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/6/2006 7:51:58 PM
|
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
|
