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
It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
You can find out the jobs owner by executing the below query.
SELECT j.name, l.[name] as [JobOwner] FROM MSDB.dbo.sysjobs j INNER JOIN Master.dbo.syslogins l ON j.owner_sid = l.sid ORDER BY j.[name]
You can change the job owner by executing the below query.
EXEC MSDB.dbo.sp_update_job @job_name = 'Job Name', @owner_login_name = 'sa'
You can check the SQL Server is cluster or standalone using one of the below technique.
Technique 1: Go to server properties from SSMS and check the Is Clustered property.
Technique 2: Execute the below query, it will retun Boolean value. (1 = True and 0=False)
SELECT
CONVERT(char(20),
SERVERPROPERTY(‘IsClustered’))
You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.
use sqlDBPool --documents table will store files into varbinary field --drop table documents create table documents ( documentID int identity(1,1), doctype char(5), document varbinary(max) ) --script to store/save document into table insert into documents Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document go select * from documents
Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.
Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011
select @@VERSION --method - 1 select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary -- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion --Method 2 select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion
aiDeeva.com 