SQLGear

While creating the build of a SQL server database project you could face following error: Error Deploy72002: Unable to connect to master or target server 'DatabaseName'. You must have a user with the same password in master or target server 'DatabaseName'. This error could be caused by the user account used in publish profile with could have sysadmin privileges. Since sysadmin users on server are mapped with user "dbo" in database hence this error. You can create or use another user with db_owner permission on the database. This could resolve this issue.

We had the requirement to migrate our cluster to a different domain. We knew that Microsoft does not recommend to migrate the existing cluster to a different domain (Read http://support.microsoft.com/kb/269196 ). The business could not afford the time to build a new cluster in the target domain and move the databases to the SQL Server instance created on target cluster. We uninstalled the SQL server after backing up each database. Server team removed the nodes from the cluster and then moved each node to a new domain. We then installed the SQL Server and attached (since time was constraint) each databases back. All the databases were running fine and we could use them. We restored the master and msdb database so that all the logins, jobs and integration packages are back in place. Later while setting up distribution for replication we faced the first error: There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_dist...

Following is the query to list all the orphan users in a SQL Server database:

Credits: David Fulmer Getting access to a production database should have a process where security team should also involved. But someone might give access to a user bypassing the process. If this sort of unauthorized users are found having access to the database during your audit reports you surly want to know about who and when. If a user was created on the database wrongly, and you want to know who created it then following script will help: It will give you HostName from where "Create User ..." was executed and LoginName of the user who executed it and Time when the user was created. USE [DatabaseName]; GO DECLARE @trace_id INT ,@filename NVARCHAR(4000); SELECT @trace_id = id FROM sys.traces WHERE is_default = 1; SELECT @filename = CONVERT(NVARCHAR(4000), value) FROM sys.fn_trace_getinfo(@trace_id) WHERE property = 2; SELECT HostName ,LoginName ,StartTime FROM sys.fn_trace_gettable(@filename, DEFAULT) WHERE EventClass = 109 AND DatabaseName = N'Databas...