:::: MENU ::::

Posts Categorized / Security and Permissions

  • Nov 12 / 2014
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions

Generate object level permissions in a database

Following script can be used to generate object level permissions in a database. We can filter the results for specific user or type.

 SELECT Us.name AS username,  
     us.type_desc AS UserType,  
     schema_name(obj.schema_id)+'.'+Obj.name AS objectName,  
     dp.permission_name AS permission ,  
     dp.state_desc AS PermissionStatus  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals Us ON dp.grantee_principal_id = Us.principal_id  
 JOIN sys.objects Obj ON dp.major_id = Obj.object_id --where us.type_desc <> 'DATABASE_ROLE'  
 ORDER BY Us.name  
 -- get user permissions on whole DB  
 EXECUTE AS USER = 'userName';  
 SELECT * FROM fn_my_permissions(NULL, 'DATABASE')   
 -- get user permissions on whole DB  
  a.class_desc, permission_name, state_desc, b.name  
 from sys.database_permissions a  
 inner join sys.database_principals b  
  on a.grantee_principal_id = b.principal_id  
  and permission_name not in ('connect','view definition','SELECT')  
  and b.type_desc in ('SQL_USER','WINDOWS_USER')  
  and state_desc = 'GRANT'  
  and class_desc = 'DATABASE'  

  • Aug 29 / 2013
  • 0
Data Modeling and Database Design, dbDigger, Security and Permissions, T-SQL Interview Questions, T-SQL Tips and Tricks

Drawbacks of using Dynamic SQL

I am performing some knowledge discovery that what may be the bad effects of using dynamic SQL in SQL Server environment. So while going through some good articles on it i have figured out following major issues/side effects of dynamic SQL

  • Query plans of Dynamic SQL are not cached so not reused
  • Dynamic SQL can put you vulnerable for SQL injection attack
  • Messy quotation marks and  spacing complicate the query writing
  • Network traffic is increased as compared to a USP executed
  • Generating the dependencies through various methods becomes unreliable as objects used in dynamic SQL can not be traced by system views
  • Ownership chaining is skipped hence permissions are compromised

These major reasons are enough to think twice before using the dynamic SQL. So use it wisely and also look for options to avoid it.

  • Aug 26 / 2013
  • 2
dbDigger, Security and Permissions, Server Level Configurations, System Administration, Xp_CmdShell

Enable and work with XP_CmdShell in SQL Server 2008 R2

Xp_CmdShell enables us to run cmd commands within T-SQL environment. The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. It requires SysAdmin rights to use Xp_CmdShell. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using proxy account. As a security measure by default Xp_CmdShell is disabled and we have to enable it explicitly before use. If disabled then following error message will be used when tried to use

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

Enable Xp_CmdShell

We may enable Xp_CmdShell through SSMS GUI or T-SQL. So let us explore both the ways to enable Xp_CmdShell.
To enable Xp_CmdShell through SSMS GUI perform following steps.

  • Right click on server instance
  • Click on Facets
  • Choose Surface Area Configuration from facets drop down list
  •  Find Xp_cmdShell from the properties and set enabled to true
  • Click OK and Xp_CmdShell is enabled now

Following snaps will help you to perform above mentioned steps for enabling Xp_CmdShell through SSMS GUI.

To enable Xp_CmdShell through SSMS GUI step 1


To enable Xp_CmdShell through SSMS GUI step 2

To enable Xp_CmdShell through SSMS GUI step 3
To enable the Xp_CmdShell through T-SQL

 -- To allow advanced options to be changed.  
 EXEC sp_configure 'show advanced options', 1  

 -- To update the currently configured value for advanced options.  

 -- To enable the feature.  
 EXEC sp_configure 'xp_cmdshell', 1  

 -- To update the currently configured value for this feature.  

verify the current option

To verify the current status of xp_cmdshell you may use following T-SQL

-- Verify the current status of xp_cmdshell  
 SELECT * FROM sys.configurations where name = 'xp_cmdshell'  

Using the Xp_CmdShell

Here i will quote a simple example of xp_cmdshell usage from BOL.
Executing the following xp_cmdshell statement returns a directory listing of the current directory.

 EXEC xp_cmdshell 'dir *.exe';  
  • 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.

  • Jul 30 / 2012
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures

List members of a specific database or server role

SQL Server provides built-in database and server roles. Members of a specific role inherit the privileges of that specific role. Such implicit privileges can not be seen in security related system tables and views. We may find members of a role both by SSMS or T-SQL.

Get members of a role through SSMS

Database roles may be found under security folder of a database. While server roles may be browsed under security folder of server instance.
To view members of a database or server role, just  right click on the role in mentioned folder. There you may get list of role members.
 Get members of a role through SSMS

Get members of a role through T-SQL

More conveniently we may get list of role members both for database role or server role. Following script would list members of sysadmin server role and db_DDLAdmin database role.

-- Get members of sysadmin server role   
 EXEC sp_helpsrvrolemember 'sysadmin'   
 -- Get members of DDLAdmin database role   
 EXEC sp_helprolemember'db_DDLAdmin'  

Role name may be substituted as required in both cases. Result would be 3 column list of role members.

  • 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.

Consult us to explore the Databases. Contact us