Logging off disconnected users

And now for something completely different… As it is essential for a DBA to know his/her way around in server administration, let’s think of something else for a minute. If your IT department consists of more than 2 people, you most likely have experienced such situation – you try to log on via RDP but there is message alike:

What happens is that standard Windows Server license allows only for 2 concurrent RDP connections to the server. If more are required, you have to install additional server role – namely Terminal Server (in 2003/2003 R2) or Remote Desktop Services (in 2008/2008 R2), but this has its consequences (primarily related to licensing).

Sometimes it’s just about sheer laziness of administrators. Instead of logging off, they just disconnect their RDP sessions still occupying a precious slot. If this is a problem, you can use command line tools available in Windows Server 2003 Admin Pack and RSAT. I’m referring here to qwinsta and rwinsta (known from Vista also as ‘query session’ and ‘reset session’):

 
D:\>qwinsta /?
Display information about Remote Desktop Sessions.

QUERY SESSION [sessionname | username | sessionid] 
              [/SERVER:servername] [/MODE] [/FLOW] [/CONNECT] [/COUNTER] [/VM]

sessionname         Identifies the session named sessionname. 
  username            Identifies the session with user username. 
  sessionid           Identifies the session with ID sessionid. p 
  /SERVER:servername  The server to be queried (default is current). 
  /MODE               Display current line settings. 
  /FLOW               Display current flow control settings. 
  /CONNECT            Display current connect settings. 
  /COUNTER            Display current Remote Desktop Services counters information. 
  /VM                 Display information about sessions within virtual machines

D:\>rwinsta /? 
Reset the session subsytem hardware and software to known initial values.

RESET SESSION {sessionname | sessionid} [/SERVER:servername] [/V]

sessionname         Identifies the session with name sessionname. 
  sessionid           Identifies the session with ID sessionid. 
  /SERVER:servername  The server containing the session (default is current). 
  /V                  Display additional information.

You can see my point here – you can find all sessions using qwinsta /server:servername and then for all disconnected users use rwinsta to force their log off. You can go a step further – prepare a list of servers (in a text file or somewhere in Active Directory) and run it against all of them. You can even schedule it if disconnected users are nuisance. Remember – you require administrative access to a server you’re querying.

@for /f "tokens=2" %i in ('qwinsta /server:server_name^| find /i "disc"') do @echo y | rwinsta %i /server:server_name

What happens here – you get the list of sessions from server and search for string “disc”. All lines satisfying this condition are tokenized and second token (being a session ID) is extracted. Then for each of those sessions forced log off is performed using session ID (by default, rwinsta asks for confirmation, therefore an echo).

Now if you have a list of servers prepared, just loop through it replacing server_name with a variable, or run your favourite LDAP query to get a list of servers from Active Directory. This is the final of this story, so if you have comments about it, feel free to share it.

Index maintenance revisited

It’s almost a month since my post on index maintenance based on fragmentation, in which I attempted to show what can be done using simple script and some DMVs. There was some discussion on LinkedIn group SQLDBA about it and I would refer to it with few comments.

1. The purpose of my script was purely to demonstrate the existence of sys.dm_db_index_physical_stats DMF, which in conjunction with other DMV yields very interesting results.
2. The script presented by me was very simple, if not simplistic. It does not work well with partitioned indexes (my own findings), offline databases (as pointed out by Sten Westerback) and – what’s more important – it’s only criteria of operation is index fragmentation being completely unaware of size or other constraints. This does not make it a good candidate for running unmodified in production environments.
3. As per another comment – indeed Microsoft provides very good example in Books Online regarding sys.dm_db_index_physical_stats, which deals with first two of points mentioned above.
4. Finally, there is a fantastic maintenance tool developed by Ola Hallengren, which gives all kinds of options when it comes to database maintenance. It is successfully used by many DBAs all around world – if you need something to be working out-of-the-box, you might want to give it a try.

The reason why I’m writing this is that I don’t want to be blamed for taking a proof of concept (as my script was) and turning it into a primary maintenance solution. As usual – read, test, ask questions, adapt, share your findings, we all will benefit from that.

Enumerating SQL Server instances in a network with Power Shell

Ever wondered how many SQL Server instances are there in your network? Or maybe you wanted to check a settings or run a script on all of them? Of course you can use Registered Servers functionality and run a query on all servers in a group, but this can save you the pain of adding all servers one by one.

Since Power Shell builds on .NET framework, you are free to use all the mechanisms available in it. The one we are interested in here is SqlDataSourceEnumerator, which allows you to enumerate all available instances of SQL Server in a local network. The only prerequisite is that servers must have SQL Server Browser service running and the task is completed by the following one-liner:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

The output you get is a table consisting of four columns – server and instance name, version and information if the server is clustered.

ServerName                                 InstanceName                               IsClustered                                Version                                 
----------                                 ------------                               -----------                                -------                                   
PHOBOS-MOBILNY                                                                        No                                         9.00.4035.00                              

Now take a simple extension of it and you are able to connect to every single SQL Server in your network. It will give you the list in standard SERVER\INSTANCE format. Then you can expand it further using SMO or Power Shell snap-in and you will get all the information you need from all servers at the same time.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()  | % {if ($_.InstanceName -eq "".ToString) { $_.ServerName + "\" + $_.InstanceName} else { $_.ServerName }}

I’m awaiting – as always – your feedback, what’s good and what still needs to be improved. Or maybe there’s just something you would like to share.

Some common problems with users and logins and their solution

After few posts devoted to indexes it’s time to look at something else. Let’s see some common problems with users and logins, and methods of overcoming them. These are the basic and most frequently occuring ones.

1. Unable to connect to server – cannot open user default database (error 4064).
   

   The message appears if the default database for a login does not exist or it’s unavailable. An example of former would be a migration, when you move some databases and logins between servers. Latter is a little bit more tricky – imagine a server which participates in database mirroring; the mirror database is in recovery all the time, so any attempts of logging into it would be rejected.
   The solution is easy – set the default database for the login to an existing one, which always available. Good practice is to use master for this purpose – master always exists and all users can connect to it. You can use SSMS for it or – if you prefer typing:

   ALTER LOGIN [testLogin] WITH DEFAULT_DATABASE=[master]

2. Unable to map login to user in a database – user, group or role already exists in the current database (error 15023).
   
   

   This message can also appear during a migration, if you copy (or move) your database (using Copy Database Wizard or detach/attach) but forget to create respective logins first, rather create logins after the database is brought online, and then try to map them to database users.

   The solution is also simple – but only T-SQL this time. You have to execute it in the database context and it works only for SQL logins (see Books Online for details of sp_change_users_login):

   sp_change_users_login 'update_one', login_name, user_name

   There is also a note in mentioned BOL article saying that from SQL Server 2008 sp_change_users_login stored procedure is considered to be removed in the future. Instead, it is recommended to use ALTER USER statement:

   ALTER USER user_name TO LOGIN = login_name

3. Orphaned database (no owner set).

   I can’t think of any reasonable explanation for that, but since I have experienced that recently, it’s worth mentioning. You restore a database from backup and it turns out that user dbo is mapped to a login that does not exist. You can do it from SSMS (Database Properties –> Files –> input database owner) or using T-SQL:

   sp_changedbowner login_name

   If you look for sp_changedbowner in the Books Online, you’ll notice that it is marked for deprecation as well. The suggested solution is to use ALTER AUTHORIZATION insted:

   ALTER AUTHORIZATION ON DATABASE::DBOUserTest to [login_name]

A few words about required privileges to complete these actions. ALTER LOGIN requires ALTER ANY LOGIN permission, but a principal can change it for its own login (this may fail if tried from SSMS since it sets CHECK_EXPIRATION and CHECK_POLICY which require CONTROL SERVER permission, at least it’s what I observed on SQL Server 2005). To remap a user with ALTER USER, you require CONTROL permission in a database. sp_change_users_login requires membership in db_owner database role. Finally, ALTER AUTHORIZATION requires TAKE OWNERSHIP on the database (on the object, in general) and might require IMPERSONATE, if you’re not logged with login taking ownership. sp_changedbowner might need CONTROL SERVER in addition to that, if new owner doesn’t have a user in a database. Since a DBA probably is a sysadmin, this is not a problem, but I have seen users in live databases yielding CONTROL which might make us wonder “do they really need it?”.

Well, this would be all about users and logins for now. If you have questions, suggestions or corrections, feel free to comment and ask. I’ll be happy to respond.