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: 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)
  • 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: SQL 2005: Help files not valid?
    ... Please try the following instructions to remove and reinstall Books Online. ... If this doesn't solve the problem, we'll need to get the Visual Studio folks ... In Add or Remove Programs, select Microsoft SQL Server 2005, and then ... Complete the remaining steps in the Installation Wizard. ...
    (microsoft.public.sqlserver.setup)
  • RE: SELECT Permissions on Schemas
    ... yet he sees all 250+ system objects listed when he goes to link ... So your real problem is that the objects in the master database deluge ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)