Search This Blog

Tuesday, December 16, 2008

Reading Sql Server and Sql Agent log files using T-SQL


It is an undocumented extended stored procedure with at least 7 parameters (default null), that calls a dll to show results.

Parameters details as:

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
  5. ? - Unknown
  6. ? - Unknown
  7. Sort order for results: N'asc' = ascending, N'desc' = descending

xp_readerrorlog 0, 2, 'ODBC', 'SQL Server',Null, Null , N'ASC'

LogDate                                   ErrorLevel     Text
2008-11-23 07:17:55.000   3                      [102] SQL Server ODBC driver version 9.00.3042

Thursday, December 4, 2008

Script to get Login's Server and Database level permissions

We have two levels for login

Server Level
Database Level

We can further divide them into Fixed roles and Permissions for a single login as: 

Server Level Permissions
Server Level Fixed Roles
Database Level Permissions
Database Level Fixed Roles

I developed a script to get such details in current environment or on current instance for all databases. Let me know if you find any issue in it.

below scripts returns following attributes as output.

LoginID: ID for database Level/Server Lever Login
Type_Descr: Login Type, SQL User/ Windows User/ Windows Group
Create Date:
Modify Date:
Class_Descr: Description of class on which permission exist, Database/Schema/Object OR Column etc.
Permission_Name: Permission description that is given to login, it contains both Database/Server Roles and Permissions
State_Desc: Grant/Deny/Revoke
DB: it contains DB name Or Server name, for which login is created
P_Type: it is flag P=Permission and F=Fixed role like db_owner, db_readonly etc.



Declare @SQL varchar(500)
            ,@Counter int
            ,@Total int
            ,@DB_Name varchar(50)
            ,@Server varchar(50

Set @Server = @@ServerName
Create Table #DBPermissions
(      LoginID int
      ,LoginName varchar(255)
      ,Type_desc varchar(255)
      ,Create_Date DateTime
      ,Modify_Date DateTime
      ,class_desc varchar(255)
      ,Permission_name varchar(255)
      ,State_desc varchar(255)
      ,DB varchar(50)
      ,P_Type varchar(1)

select identity(int,1,1) id,name,database_id into #DBs from sys.databases

Set @Total = @@RowCount

Set @Counter = 1

While @Counter<=@Total
      Select @DB_Name = name From #Dbs where id = @Counter

      -- Database Permissions
      Set @SQL = 'select Logins.Principal_ID,, Logins.Type_desc, Logins.Create_Date, Logins.Modify_date, P.class_desc, P.Permission_name, P.State_Desc, ''' + @DB_Name + ''',''P'' from ' + @DB_Name + '.sys.database_principals Logins Left Outer Join ' + @DB_Name + '.sys.database_permissions P On P.grantee_principal_id = Logins.Principal_ID          where Logins.type not in (''A'',''R'',''C'',''K'') And P.Permission_name is not null'
      Insert into #DBPermissions
      Exec (@SQL)

-- Database Fixed Role
      Set @SQL = 'select member_principal_id,M.Name , M.type_Desc , M.Create_Date, M.Modify_date, '''',, '''', ''' + @DB_Name + ''', ''F'' from ' + @DB_Name + '.sys.database_principals Roles Inner join ' + @DB_Name +.sys.database_role_members DRoles on Droles.role_principal_id = Roles.Principal_ID And Roles.type  in (''A'',''R'',''C'',''K'') Inner join ' + @DB_Name + '.sys.database_principals M On M.Principal_ID = Droles.member_Principal_ID And M.type  not in (''A'',''R'',''C'',''K'')'

      Insert into #DBPermissions
      Exec (@SQL)

      Set @Counter = @Counter + 1


-- Server Logins Permission
Insert Into #DBPermissions
select SLogins.Principal_ID,, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date, P.Class_Desc, P.Permission_name, P.State_Desc,@Server,'P'
from sys.server_principals SLogins
Inner Join sys.server_Permissions P on P.grantee_principal_id = SLogins.Principal_ID
where SLogins.type not in ('R','C','K')

-- Server Fixed Roles
Insert Into #DBPermissions
select Roles.member_principal_id,M.Name, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date,'',, '', @Server, 'F'
from sys.server_principals SLogins
Inner join sys.Server_Role_Members Roles On roles.role_principal_id =Logins.Principal_ID And SLogins.type in ('R','C','K')
Inner join sys.server_principals M On  roles.member_principal_id = M.Principal_ID     And M.type not in ('R','C','K'


Drop Table #DBPermissions

Drop Table #DBs