SQL SERVER 2005 Permissions to View Stored Procedures

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master
GO
REVOKE VIEW ANY DEFINITION TO User1

-- or

USE AdventureWorks
GO
REVOKE VIEW Definition TO User1


If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Linked Server >>> Unable to begin a distributed transaction

Error Message:

OLE DB provider "SQLNCLI" for linked server "agosqltqtest01_link" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "test01_link" was unable to begin a distributed transaction.

Resolution: Change to No Authetication Required as follows