Re: Permission to view system tables in master database



Dave (Dave@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I am trying to avoid the "Permission denied" error message when a proc
tries to select records from one of the system tables in a SQL Server
2005 master database.

Sepcifically I need to be able to capture the IP address of the calling
user which I can get from sys.dm_exec_connections.

I have struggled with this for some time and could not find anything
directly on point in MSDN. However, I was able to cobble something
together that "appears" to work as shown below.

I am hoping that someone who has specific experience with this issue
might be able to comment on my approach and point out any issues or
deficiencies.

Yes, you have to do something you did, although you don't the EXECUTE
AS thing. Certificate signing is all you need. I have an article on
my web site that show how you can do this:
http://www.sommarskog.se/grantperm.html

Since you seem to have grasped some of this already, you may want to
rush through until the first BULK INSERT example.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.



Relevant Pages

  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Master database corrupt.
    ... SQL Server isn't working at all. ... Or a "recreate master database" command? ... If you have a backup of the master database, ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.server)
  • Re: Linked Server: How to check if server exists?
    ... The root problem is described in the Books Online topic Batches. ... Rick Byham, SQL Server Books Online ... linked server don't run on those development machines not having that linked ...
    (microsoft.public.sqlserver.connect)
  • Re: STORED PROCEDURE - passing table name as a parameter
    ... T-SQL, is that you get problems if the view definition does not fit into ... will need to query other system tables, for instance syscolumns. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Problem when changing Views
    ... There aree several reasons. ... I use neither of the tools, but I guess that Design View resubmits the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)