Archiv der Kategorie: SQL

Microsoft SQL Server – Orphaned users ()

In short: If you restore a database – which was backed up on a different server – and want to create a server login and map it to your database user, you may get an error message. Reason: the user already exists in your database and cannot not be re-created. So you have to find the orphaned user in your database;

use YOURDBNAME;

GO;

EXEC sp_change_users_login ‚Report‘;

The result should be something like this:

UserName|UserSID dbo|0x6975A7DA42EE2440BDF292BF4D8D338F

You can now link the login to the db user or create a new Login with the resulted SID above (tested, and working):

CREATE LOGIN [YourSQLServerLogin] WITH PASSWORD = 0x02003A95B96423C3D260F29BE27C39B139426666AEA07E8B8EBA75FG8F4EABB7ACBEEAF9E5E3510A58735E396309ABF3E9DF047378352B8868A0FBDF0BD8EEFE66CAE6C14963 HASHED, SID = 0x6975A7DA42EE2450BDF222BC4D8D338F, DEFAULT_DATABASE = [master]

Link an existing Login to the db-user:

EXEC sp_change_users_login ‚update_one‘, ‚db_login1‘, ‚db_login1‘;

Unfortunatelly ‚dbo‘ is an unallowed argument to the procedure sp_change_users_login … – so i’ve done with creating a new user 🙂 Afterwards you can manage your Login like before via SQl-Statments and/ or SQL Server Management Studio. Additional Links:Understanding and dealing with orphaned users in a SQL Server database https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/ http://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users https://msdn.microsoft.com/en-us/library/ms175475.aspx

Change the owner of the database:

SQL Server 2008 and earlier:

use somedb
go
sp_changedbowner [ @loginame = ] 'login'

SQL Server 2012 and alter:

use master
 go
 ALTER AUTHORIZATION
 ON database::[Test-DB]
 TO [someLogin]

Documentation on Alter Authorisation can be found here: https://msdn.microsoft.com/de-de/library/ms187359.aspx

SQL Server – Query to find a column in every table in all tables

Source: http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

 

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‚%EmployeeID%‘
ORDER BY schema_name, table_name;

SQL Server 2012 Availability Groups

Deploying AGs:

How to Set Up SQL Server 2012 Always On Availability Groups

Specials about user managment:

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/07/16/contained-databases-in-sql-server-2012.aspx

http://blog.fumus.de/sql-server/contained-databasedie-eigenstndige-datenbank#Migration_von_Datenbanken_und_Benutzern_in_Contained_Databases

Lessons learned:

AlwaysOn Availability Groups Real-Life Lessons Learned (Video)

 

AV-Groups on VMware:

https://www.vmware.com/files/pdf/solutions/SQL_Server_on_VMware-Availability_and_Recovery_Options.pdf

 

Upgrading AlwaysOn Availability Group Replica Instances

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