Give Access to check Activity monitor and to run SQL Profiler for non sysadmin login.

Scenario:

Need to get permission on the server that will enable to see the activity monitor and to work with the SQL Server Profiler on sql server.

Those permission are needed for testing the work done on the Test DB

--------------------------------------------------------------------------------------------

How to give access to check Activity monitor?

USE master;

GRANT VIEW SERVER STATE TO [windowslogin]

How to give access to run Sql Profiler on the server to one windows login i.e. non sysadmin?

USE master;

GRANT ALTER TRACE TO [windowslogin]

Or

Security tab --> Logins --> right click name to modify select properties --> Securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list

You need to check the Grant column for the required permissions.

How to grant permission to view database objects definition?

Scenario:

Customer asked DBA to grant permission to view recently created stored procedure script in SQL.

How will we do?

Solution:

For example the stored procedures sp1, sp2 and sp3 are created recently. User asking view access only for stored procedure objects .Not executes permission.

How will you do?

By using this query, we can do.

USE databasename

GO

GRANT VIEW DEFINITION ON sp1 TO sqlloginname;

GRANT VIEW DEFINITION ON sp1 TO sqlloginname;

GRANT VIEW DEFINITION ON sp3 TO sqlloginname;

View definition role allows user to view the corresponding objects

Any other methods?