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>'
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#.
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
SELECT 'Authentication Method'=(
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count'
GROUP BY login_name,nt_user_name