:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Jun 26 / 2012
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions, T-SQL Tips and Tricks

Impersonating a login or user in SQL Server

SQL Server deals with Logins at server level and in the databases with users. Databases operate with various permissions on different objects. Permissions are assigned explicitly or implied. It is one of the primary responsibilities of a DBA to monitor and manage the permissions so that data and information security is ensured.
As part of managing the security and permissions architecture, DBA may be required to check a specific context or permission  for any account. You do not need to get password from the account owner to get login and then check the context.A simple and effective method is available. Just use the EXECUTE AS statement and you are in required context.
Actually EXECUTE AS is meant to manage more than just this simple task. We may use it for stored procedures context as well. EXECUTE AS may be used in two flavors

  • EXECUTE AS Login

As the above mentioned flavors indicate, while working with server level permissions EXECUTE AS Login may be used to impersonate the server level permissions of a login. While within a database context, EXECUTE AS User may be used to switch the context to a specific user in a DB. Before going on usage of EXECUTE AS, let me clarify that not every one may impersonate by using the EXECUTE AS. sysadmin and dbo has impersonation rights already with them at server and db level respectively. However impersonation permissions may be granted to required login/user. Discussing the internals and flow of impersonation is beyond the scope of this post.
Following is the code and result to prove the impersonation of a login by using EXECUTE AS Login

Impersonate SQL Server Login by Using EXECUTE AS LOGIN

We may analyze that login context was switched and was revert at the end. According to BOL impersonation context is changed back in following three conditions.

  • Another EXECUTE AS statement is run
  • A REVERT statement is run
  • The session is dropped

Following code and result would demonstrate the impersonation of a user through EXECUTE AS User statement

Impersonate SQL Server DB User Using EXECUTE AS USER

Impersonation through EXECUTE AS statement was intrioduced in SQL Server 2005. Before this SETUSER statement was used. SETUSER may still be used in new versions but as a deprecated feature.
Also read a EXECUTE AS related tip on mssqltips.com.

  • Feb 28 / 2012
  • 0
dbDigger, Monitoring and Analysis, Reference Articles Archival, SQL Server logs, System Stored Procedures, T-SQL Tips and Tricks

Reading SQL Server logs by using T-SQL

SQL Server logs are valuable way to analyze condition and activities on SQL Server. Activities may be related to logins, sessions, backups, permission errors etc. SSMS GUI provides way to read SQL Server logs however also there are powerful T-SQL alternates that provide facilities filters for dates, keywords and specific log files. Click here to read a very well written article about reading SQL Server logs through T-SQL. Also do not forget to read valuable comments under this article.

  • Dec 05 / 2011
  • 0
Data Modeling and Database Design, dbDigger, Monitoring and Analysis, T-SQL Scripts

Analyze all foreign keys in your SQL Server database

Creating foreign keys ensure data integrity across different tables in a database. However only creating these is not enough. Keeping in view the requirement we have to configure proper impact on foreign key value when its primary key is affected by update or delete operation.
SQL Server provides four rules for implementing the effect of primary key on foreign key value in case of update or delete operation. These are

  • No Action
  • Cascade
  • Set NULL
  • Set Default

In my related article on MSSQLTips, I have discussed the configuration and effect of these four rules. The emerged problem is to analyze the existing foreign keys for their configured rules for update and delete operations. It is required to generate a list of all foreign keys along with their associated columns and rules for update and delete operations. Below is the script that would provide a brief information to start with

SELECT fkey.name AS FKeyName,  
 object_name(fkey.parent_object_id) AS FKeyTable,  
 cols.name AS FKeyColumn,  
 object_name(fkey.referenced_object_id) AS PKeyTable,  
 colsa.name AS PKeyColumn,  
 CASE is_disabled WHEN 1 THEN 'YES'  
 WHEN 0 THEN 'No' END AS IsDisabled,  
 delete_referential_action_desc AS DeleteRule,  
 update_referential_action_desc AS UpdateRule  
 FROM sys.foreign_key_columns FKeyC  
 INNER JOIN sys.foreign_keys fkey  
 ON fkeyc.parent_object_id = fkey.parent_object_id  
 AND fkeyc.referenced_object_id = fkey.referenced_object_id  
 INNER JOIN sys.all_columns cols  
 ON fkeyc.parent_object_id = cols.object_id  
 AND parent_column_id = cols.column_id  
 INNER JOIN sys.all_columns colsa  
 ON fkeyc.referenced_object_id = colsa.object_id  
 AND parent_column_id = colsa.column_id  
 ORDER BY is_disabled DESC, delete_referential_action_desc, update_referential_action_desc  

You may also add create_date and modify_date in the select list for creation and modification date of respective foreign key. Once the list is available, analyze the requirement and configured rule along with disabled foreign keys.

Analyze all foreign keys in your SQL Server database

  • Apr 07 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get code of user stored procedures and UDF in a database

If one is required to search for any column or hard coded value in SP/UDF code then access to code definition is required. So here is a script to generate code of all SP and UDF in a database. This script may be utilize to

  • Check for any specific column or hard coded value in all SP/UDF
  • Check for dependency of any object by providing the object name as like parameter for code

Following script would generate all user SP along with their code.

USE [DB_Name_here]  
 SELECT ROUTINE_NAME [SP Name], routine_definition [SP Code]  
 ON r.[ROUTINE_NAME] = o.[name]  
 AND [is_ms_shipped] = 0  
 ORDER BY routine_name  

And with a little modification of above script we may also get list and code of all UDF in a database.

 USE [DB_Name_here]  
 SELECT ROUTINE_NAME [UDF Name], routine_definition [UDF Code], type AS [UDF Type]  
 ON r.[ROUTINE_NAME] = o.[name]  
 WHERE [is_ms_shipped] = 0  
 AND type in ('FN','TF','IF')  
 ORDER BY type,routine_name  

Do not forget to provide proper database name in use database statement.

  • Apr 06 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get list of Stored procedures or UDF with creation and modification dates

For monitoring and analysis purpose a broad view about creation and modification of SP and UDF in a database was required. I have created two scripts to get all SP and UDF with their creation and last modification dates.

 Get all User SP along with Creation and last modification dates  
 USE [DB_Name_here]  
 SELECT name, create_date, modify_date  
 FROM sys.[objects]   
 WHERE type = 'P'  
 AND [is_ms_shipped] = 0  
 ORDER BY [create_date] desc, modify_date DESC   

And in case of UDF they may be scalar, table valued or in-line table valued. You may generate the list of any type by providing following parameters for type column.
Scalar UDF = FN
Table valued = TF
In-Line Table Valued = IF

In following script i have included all three types in list of UDF.

 Get all UDF along with Creation and last modification dates  
 USE [DB_Name_here]  
 SELECT name, create_date, modify_date, type  
 FROM sys.[objects]   
 WHERE type in ('FN','TF','IF')  
 AND [is_ms_shipped] = 0  
 ORDER BY type,[create_date] desc, modify_date DESC   

For both of above scripts do not forget to provide/select required database name. Table used in these scripts is sys.Objects. Click here to get further details of columns and parameters in this table.

  • Nov 30 / 2010
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, System Stored Procedures

Get SQL Server logins with same password as Login name

Standards demand that password should not be same as login name. Same standard should be implemented for SQL Server logins. So how to check that either a login on your server has same password as login name itself?
As passwords in SQL Server are stored as varbinary(128) and are not readable, so we have to use dedicated system stored procedure for this purpose. Just use following script to get SQL Server LogIns with same password as login name itself.

USE [master]

SELECT LogInName ,createdate
FROM syslogins
WHERE pwdcompare (name,PASSWORD)=1

Script would display name and creation date of any login which has same password as login name. Here in my case it returned a login which i just created with same password as logIn name.

Get SQL Server logins with same password as Login name

The system stored procedure used in above script to compare LogIn name and password is PWDCOMPARE. Click here to read more about this system stored procedure.

Consult us to explore the Databases. Contact us