November 12, 2003 at 3:19 am
Hi!
Use this code:
EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ' + @table_name)to get a list of permissions for a table on another server.
When no permissions exist I get:
quote:
Server: Msg 15330, Level 11, State 1, Line 1There are no matching rows on which to report.
Is it possible to replace this message with custom message like this:
'Permissions on table ' + @server_name + '.' + @db_name + '..' + @table_name + ' do not exist'
(without creating custom analog of sp_helprotect)
Perhaps I need something like:
SET [DO NOT PRINT ERROR MESSAGES]
EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ' + @table_name)
IF @@ERROR<>0 PRINT 'Permissions on table ' + @server_name + '.' + @db_name + '..' + @table_name + ' do not exist'
Thanks.
November 12, 2003 at 3:38 am
This might help
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=18119
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 12, 2003 at 7:27 am
One way is to use temp table
SET NOCOUNT ON
CREATE TABLE #temp (
Owner varchar (36),
Object varchar (62),
Grantee varchar (18),
Grantor varchar (36),
ProtectType varchar (11),
Action varchar (10),
[Column] varchar (38))
INSERT INTO #temp
EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ')
IF EXISTS(SELECT 1 FROM #temp WHERE Object = @table_name)
BEGIN
SELECT * FROM #temp WHERE Object = @table_name
END
ELSE
BEGIN
PRINT 'Permissions on table ' + @server_name + '.' + @db_name + '..' + @table_name + ' do not exist'
END
DROP TABLE #temp
SET NOCOUNT OFF
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply