Category: SQL Server
-
Script to create a Database with different collation
One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.
create database SQLDB collate Latin1_General_CS_AS; go create database SQLDB1 collate Latin1_General_CI_AS; go SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB; SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;
-
Script to drop all SQL Logins
Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.
Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”
SA login account and the few policy certificates also come in SQL Login type, I have excluded them.
select * from sys.server_principals where name = 'sa' or substring(name, 1, 1) = '#'
select 'DROP LOGIN ' + [name] from sys.server_principals where type_desc = 'SQL_LOGIN' and sid <> 0x01 and substring(name, 1, 1) <> '#'
Next step is to execute the output of the above query.
-
Steps to Drop an Orphan SQL Server User when it owns a Schema or Role
Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.</code
