:::: MENU ::::

Posts Categorized / Logins and Users

  • Nov 06 / 2012
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

Get current connection context through SUSER_SNAME

If you are required to get the current connection context then SUSER_SNAME() function may be used. Primarily it is meant to return the user when security identification number (SID) is passed as parameter. However without passing parameter it would return the current connection context in terms of user. This information may be more helpful when used along with impersonation. Here is a simple example to use the SUSER_SNAME.

SELECT SUSER_SNAME() 
GO
  • 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
  • EXECUTE AS User

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 16 / 2012
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

  1. Login is requesting to connect from inside the application code
  2. Login is requesting from no where else but web server (application server)

My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 DECLARE @ip VARCHAR(16)  
 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 ((SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 or @ip <> '192.168.1.202') -- Provide IP address of web server here  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

In this way the login used for application is restricted to access only from web server through application code only.

  • 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]
GO

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

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.

  • Jun 25 / 2009
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict SQL Server login to connect from out side the application through logon trigger

There is a situation that I have a SQL Server login with rich rights in some of databases. It is used for connection through a .Net application. It is not assigned to any one for use in SSMS etc. I want to make sure that this login would not be used for connection to SQL Server out side the application. While looking at various possible options i ca,e to know LogOn triggers in SQL Server.
Logon triggers in SQL Server are used to carry out logon related customization and tasks. From onwards to SQL Server 2005 SP2, we can create logon triggers that are meant to be fired every time when a session is established to SQL Server. If a login is not successfully authenticated then logon trigger is not fired in that case.
Consider following example to implement the above requirement. We will restrict the user AppUser to connect to SQL Server through .Net application only.

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 (SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

Trigger has been created and user ‘AppUser’ is no more able to connect to SQL Server through any means other than .Net application.
Logic implemented in if block may be modified to implement a large number of other customizations that are required.
Logon triggers are created in master database. And may be viewed in SSMS in server objects folder.

LogOn Trigger in SSMS

While trying to log in SSMS through logon trigger following message is generated, showing that created logion trigger is working fine.

LogOn Restricted through LogOn trigger

It is important to mention that be attentive while working with logon trigger, some wrong or missing conditions may block all SQL Server logins to log in to SQL Server. Best solution is that be logged in through any user in SSMS while testing logon trigger. If any problem occurs due to trigger you can use already logged in user to manage the situation.
Even if you do not have a log in connected and a problem occurs due to logon trigger then work according to this article.
Click here
to get T-SQL commands to enable or disable the SQL Server logon trigger

Pages:1234
Consult us to explore the Databases. Contact us