Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 23rd, 2005, 05:47 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Grant permissions on object from other DB

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.

 
Old December 23rd, 2005, 07:03 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 + ']'




 
Old December 27th, 2005, 01:01 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old December 28th, 2005, 02:33 PM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old January 6th, 2006, 08:51 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Permissions on DB without user-level security nbourre Access VBA 4 June 7th, 2008 08:09 AM
grant access---urgent. Komila Oracle 1 March 21st, 2007 03:57 AM
Restrict ASP.NET app DB permissions using Integrat wirerider ASP.NET 2.0 Professional 0 March 6th, 2007 08:23 PM
Sequence grant in Oracle rekha_jsr Oracle 1 January 3rd, 2005 12:50 AM
File System Object Permissions kilika Classic ASP Basics 2 August 6th, 2003 09:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.