Sql-server – In SQL Server 2005 how would I determine the minimal permissions a user requires

sql serversql-server-2005

I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices. Currently every login is a member of the sysadmin group.

What is the easiest way to determine the minimal amount of privileges needed for each account? I know I can use SQL Server Profiler to log all the SQL commands executed but it will return a bunch of information. I can not think of an easy way to summarize all that data.

Any help would be appreciated.

Best Answer

If you want to cut the noise out of a Profiler trace, ClearTrace is invaluable. ClearTrace “normalizes” queries, removing parameters such that:

SELECT x FROM y WHERE z = 1
SELECT x FROM y WHERE z = 2

are both reported as

SELECT x FROM y WHERE z = #

If you capture sufficient activity from the database and order the ClearTrace report by execution count, you’ll have a better idea of the common/typical query patterns and from this the permissions required. You can then investigate the non-typical activity and establish if these require a different set of permissions and/or are generated by a subset of the user base.

Related Solutions

Sql-server – Execution plan issue requires reset on SQL Server 2005, how to determine cause

Probably Parameter Sniffing. To troubleshoot this without running Profiler

1) Add a GUID into the text of the query as a comment. e.g.

/*ED7446E4-03B1-4279-AB43-6FFE800AD860*/

2) Wait for the problem to recur so that it runs slow from the application and fine from SSMS.

3) Compare the query plans for the 2 different environments (retrievable from the below)

SELECT *
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
       CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE  t.text LIKE '%ED7446E4-03B1-4279-AB43-6FFE800AD860%'
       AND epa.attribute = 'set_options'

Sql-server – Permissions on sys.syslogins

You can use SUSER_ID which will give you the SID from sys.server_principals regardless of metadata visibility

Also note that syslogins has been deprecated since SQL Server 2005

Best Answer

Related Solutions

Sql-server – Execution plan issue requires reset on SQL Server 2005, how to determine cause

Sql-server – Permissions on sys.syslogins

Related Question