I was about to grant a user permission to execute all stored procedures in a database, when i realized i didn’t have a good method to see what permissions to execute that user currently had. I searched for a solution. In doing so, i found a number of sql queries that i either had never used or hadn’t used recently. …
Various Select statements to remember!
select * from information_Schema.table_privileges
select * From sys.crypt_properties
select * from sys.linked_logins
select* from sys.procedures where name not like ‘Syclo%’ and name not like ‘dt_%’ and name not like ‘sp_%’
SQL Server 2005 improves on SQL 2000 by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader.
Grant Execute to thisUser