Category Archives: Security Audit

How to: Remove SQL Server Database User(s)

Database security is one of the significant concerns for most DBAs. DBAs frequently restore or backup the database, this is a very common scenario, But the thing is after successfully restoring a new version of your database, you want to remove the current users. Probably you thought of just expanding the user node and deleting the desire user; in that sense you are somewhat correct. But if you face an error like:

Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.

EXEC sp_dropuser '<USER_NAME>'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<USER_NAME>')
DROP LOGIN '<USER_NAME>'

EXEC sp_droplogin '<USER_NAME>'

References:

  • http://www.codeproject.com/Articles/66827/Few-Step-s-to-Remove-SQL-Server-Database-User-s
  • http://msdn.microsoft.com/en-us/library/aa933285%28v=sql.80%29.aspx

How to: Enable CLR Integration

In an effort to improve security, Microsoft has turned many features “off by default”. The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Once you have the CLR enabled, you can write user-defined functions, triggers, stored procedures, user-defined aggregates, and user-defined types in VB.Net and C#.

How to: Check Database User Permission

The following scripts was shared by Pradeep Adiga in his blog to check the permission granted/denied for the databases users. It is very useful when you need to check whether the user has permission for those stored procedures in SQL Server databases.

SELECT
    USER_NAME(dppriper.grantee_principal_id) AS [UserName],
    dppri.type_desc AS principal_type_desc,
    dppriper.class_desc,
    OBJECT_NAME(dppriper.major_id) AS object_name,
    dppriper.permission_name,
    dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id

References: