This is one very interesting issue which I have started to follow recently. Just like any other organization my company has many servers. Some are production and some are development. It is very much necessary that query which are written for developer environment does not run for production environment accidentally.
In SQL Server 2008 there is special feature which can change the color of the task bar. This will alert developer to run query on server.
Let us see quick tutorial with images which explains how the color of the status bar in SQL Server management studio can be changed.
I got following error when I was trying to delete user ‘test’ with ‘SA’ login. I was little surprised but then I tried to delete with the windows authenticated systemadmin account. Once again I got the same error.
Msg 15151, Level 16, State 1, Line 3
Cannot drop the login ‘test’, because it does not exist or you do not have permission.
The reason I was surprised that I was systemadmin and I should be allowed to delete the login. I am including the script which I used to delete the account here.
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Test') DROP LOGIN [Test]
I finally found out that error was misleading. I had another session open in SQL Server Management Studio with username ‘test’ and it was preventing me to drop the user. Once I closed that open session I was successfully able to delete the user using above script.
In Normal Case:
Following error usually show up when user is logged in, which I did not get it. Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.
However, when I tried to re-create scenario I got above error. I want to share this experience with users and want to know if they have ever faced this scenario.
The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.
The question I had received is following: “How to add any user to systemadmin role?“
This is very simple process.
Method 1: T-SQL
GO CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin' GO
Msg 15151, Level 16, State 1, Line 2
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.
This error had occurred because of insufficient rights. Please read my previous post here before reading further article.
SA is system admin user and it is the highest level of user in system. If any user have to modify the permissions of SA that user needs to have higher or equivalent rights as SA user. Users member of systemadmin group are can only change the rights of SA user.
First adding any user to systemadmin role and then using the same user to modify system admin’s tool will fix the issue.
GO /* Create Test Table */ CREATE TABLE TestTable (FirstCol VARCHAR(10)) GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Change the database collation */ ALTER TABLE TestTable ALTER COLUMN FirstCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL GO /* Check Database Column Collation */ SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TestTable') GO /* Database Cleanup */ DROP TABLE TestTable
When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.
Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.