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>')

EXEC sp_droplogin '<USER_NAME>'



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;
sp_configure 'clr enabled', 1;

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.

    USER_NAME(dppriper.grantee_principal_id) AS [UserName],
    dppri.type_desc AS principal_type_desc,
    OBJECT_NAME(dppriper.major_id) AS object_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