Category Archives: Upgrade

Script to De-Orphan SQL Logins

–By Lori Brown   @SQLSupahStah

I am working with a client who is migrating from SQL 2012 to SQL 2016. It just so happens that while they have just a few databases to move, they also have a ton of SQL logins that needed to be migrated. This is not an issue since I know to use sp_help_revlogin to script logins and passwords into a script that can be applied to the new SQL 2016. However, inevitably, there are SQL logins that have been deleted from SQL but not from the database or while testing on SQL 2016 the databases are refreshed and now logins are orphaned. I needed to have a script that would look at each database and give me a script that could be used to create or de-orphan these logins.

I figured that with a little Google-fu I would find someone who had already created a way to do this. And, while I did find a solution, I decided to give it my own twist. My solution is to have a script that I can run when needed that will output the statements that I need to create or de-orphan logins from all databases in an instance. I combined things from these two blogs:

http://www.sqlshack.com/creating-a-stored-procedure-to-fix-orphaned-database-users/

https://www.codeproject.com/articles/489617/create-a-cursor-using-dynamic-sql-query

Here is my code and a quick look at the output:

USE master

SET NOCOUNT ON

DECLARE @usercount INT

DECLARE @sqlstr1 NVARCHAR(1000)

DECLARE @sqlstr2 NVARCHAR(500)

DECLARE @dbname VARCHAR(128)

DECLARE @username SYSNAME

DECLARE @OrphUsers AS CURSOR

 

DECLARE db CURSOR FOR

SELECT name FROM sys.databases

WHERE name NOT IN (‘master’,‘tempdb’, ‘msdb’, ‘model’)

AND is_read_only = 0 AND state = 0

 

OPEN db

FETCH NEXT FROM db INTO @dbname

WHILE @@FETCH_STATUS=0

BEGIN

 

SET @sqlstr1 = ‘SET @orphanedusers = CURSOR

       FOR SELECT a.NAME

       FROM [‘+@dbname+‘].sys.database_principals a

       LEFT OUTER JOIN sys.server_principals b

             ON a.sid = b.sid

       WHERE (b.sid IS NULL)

            AND (a.principal_id > 4)

             AND (a.type_desc = ”SQL_USER”)

 

       OPEN @orphanedusers’

 

EXEC sys.sp_executesql @sqlstr1, N’@orphanedusers cursor output’, @OrphUsers OUTPUT

 

FETCH NEXT FROM @OrphUsers INTO @username

WHILE @@FETCH_STATUS=0

BEGIN

 

SELECT @usercount = COUNT(*) FROM sys.server_principals WHERE name = @username

 

IF @usercount = 0

BEGIN

PRINT ‘–User ‘+@username+‘ does not exist and must be created. Please use a strong password.’

SET @sqlstr2 = ‘USE [master] CREATE LOGIN ‘ + @username + ‘ WITH PASSWORD = ”ABC123”’

END

             ELSE

             BEGIN

                    PRINT ‘–User ‘+@username+‘ is an orphan.’

SET @sqlstr2 = ‘USE [‘+@dbname+‘] ALTER USER ‘ + @username + ‘ WITH LOGIN = ‘ + @username

END

 

PRINT (@sqlstr2)

FETCH NEXT FROM @OrphUsers INTO @username

END

CLOSE @OrphUsers

DEALLOCATE @OrphUsers

 

FETCH NEXT FROM db INTO @dbname

END

CLOSE db

DEALLOCATE db

 

SET NOCOUNT OFF

blog_20161229_1

The output labels what needs to be done to make sure the users have access to their databases. If the user does not exist in SQL at all then a CREATE LOGIN statement is generated. If the user is simply orphaned an ALTER LOGIN statement is generated.

The only thing I did not do yet (mostly because my needs were centered around SQL logins) is to include Windows domain accounts in my logic. As soon as I add that to my script, I will repost. Or, if someone out there adds it, please send an update.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

Consistency Check an Analysis Services Database

— By Lori Brown @SQLSupahStah

I am migrating a client to SQL 2016 and had to restore the SSAS databases and do what amounts to a DBCC CHECKDB against it. The command is done while connected to SSAS in Management Studio. You open an XMLA query (not MDX)…

blog_20161209_1

…and run the following:

<DBCC xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine>

<Object>

<DatabaseID>Work Metrics</DatabaseID>

<CubeID>Hourly Insights</CubeID>

</Object>

</DBCC>

The output looks like this:

blog_20161209_2

As usual more info can be found from MSDN: https://msdn.microsoft.com/en-us/library/mt156975.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL Server 2016 RTM Available!!

In case you live under a rock or were at the dentist, Microsoft announced that SQL Server 2016 has been made generally available today June 1, 2016. See: https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/

Pricing is as follows:

Blog_20160601_1

A handy feature comparison of previous versions of SQL can be located here:

https://www.microsoft.com/en-us/server-cloud/products/sql-server/comparison.aspx

SQL 2016 feature comparison between editions can be found here:

https://msdn.microsoft.com/en-us/library/cc645993.aspx

Let the upgrades begin!! Everything upgrade related can be found here: https://msdn.microsoft.com/library/bb677622.aspx

We are excited to be working with this new version! Give us a shout with questions and stories of your SQL 2016 experiences!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Master Database Compatibility Level

If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases.  This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade.  This has the negative side effect of not being able to use operators, keywords and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level.

For instance, if you want to examine currently executing queries using SQL 2005 with your master database in 80 compatibility …

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 – s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

… you will receive the following error…

Msg 321, Level 15, State 1, Line 5

“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

As a workaround, you can execute the same query against tempdb, model or msdb and it will work with no errors.  It is permissible to change the compatibility level of the master database should you find it in a low compatibility level.

–SQL 2005

EXEC sp_dbcmptlevel ‘master’ , 90

GO

–SQL 2008

EXEC sp_dbcmptlevel ‘master’ , 100

GO

http://msdn.microsoft.com/en-us/library/ms178653.aspx

http://technet.microsoft.com/en-us/library/bb933942.aspx