Creating user without mentioning the login name

As a sysadmin, I create a login:

CREATE LOGIN BobSmith WITH PASSWORD = 'mypassword'

I now am in the Sales database and execute this:

CREATE USER BobSmith

What happens?

The user BobSmith is created and mapped to the login BobSmith

If a login name exists with the same name as a user, it is automatically mapped.

Source: SQL Server Central QoD(2015/12/29).

Ref: CREATE USER - https://msdn.microsoft.com/en-us/library/ms173463.aspx

SCRIPT to FIND Object Level Permissions.

USE DBNAME

select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id

--where p.grantee_principal_id=11


--select USER_ID('GROUP\ACC_S671A0004_Complyman_Admin_RW')

--select USER_NAME(grantee_principal_id),grantee_principal_id from sys.database_permissions

Creating SQL Server login using hashed password

When we restore a database in target instance, along with database we need to fix the Orphaned logins.

If target instance has the respective login we can directly fix the orphaned users using below query:

exec sp_change_users_login 'auto_fix' or
exec sp_change_users_login 'update_one'


If the login does not exists on the target instance we need to copy that login from Source to target.
As all the passwords for SQL logins are in encrypted mode, we cannot see that password. For this type of situation we can copy the logins by fetching the hashed password, using below query


--- To generate Hashed paaword

SELECT LOGINPROPERTY('Loginname','PASSWORDHASH')




-- To create the login




CREATE LOGIN [Login Name] WITH PASSWORD = [Hashed password] HASHED;<br />

This will create the login with same password as in source instance.

Now you can proceed with fixing the orphaned users.

New Feature Of SQL Server 2016

We can add multiple TempDB files while installing SQL Server itself.
In Previous versions we can add TempDb files only after installation.

SQL Server user not able to view any objects

In my regular job, one day one of my client reported that he is not able to view the database objects from his application.

So I have checked for permission on that user, it already has db_read and db_write permissions. So he should able to view the objects.

I checked for login details so that I can test whether he is connecting to instance properly or not. Using those login information I am able to connect to SQL Server management studio and able to query the tables from Managment studio and also using T-SQL.

Now what is the Problem ?

Using that login able to query the objects from SSMS but not from applicaiton, why.

Checked the error logs, no use.

After struggling for some time, as trail changed the default database for that login, and mapped to the database on which he is trying to query.

Ahhh.... It worked......

Client is able to query the tables from application.

:)

When to use Update_one and when to use AutoFix

We can use Update_one when the Database user name and login name are different.

AutoFix can be used when both Database user name and login name are same.

And auto fix can be used when login does not exist, if you provide password new login will be created with the provided password, if you have not passed any password, sql server will generate password then creates the new login.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'

GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

To Get the OS Name and version and also other servers

systeminfo | findstr /B /C:"OS Name" /C:"OS Version"

To get the OS Version of other server:   get-wmiobject win32_operatingsystem -computer TK3ECITXSQL007 | select pscomputername,Version

This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

-- Enabling/Disabling SQL agent jobs on AO instances.

-- <Raghavender Chavva>

-- <20^th March,2014>

-- Last Modified: NA

-- <This query is not suitable for SQL Server versions which are below SQL Server 2012 and SQL Servers which are not in Always On>

--******************************************************************************

--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED

--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

--* PARTICULAR PURPOSE.

--*

--******************************************************************************

-- < This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.>

-- Step 1: -- ---checking instance replica status (here Primary)

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY') 

begin

-- Step 2: -- Colleting the job names

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history')

-- select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

--- Step 3: Enabling the required jobs.

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

-- Step 4: -- ---checking instance replica status (here Secondary)

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY')

–- Step 5:  Colleting the job names

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog')

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

–- Step 6: Disabling the jobs

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0'

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end

this script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

Below is the script with comments, please let me know if I need to add more comments:

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY')   ---checks whether the instance is primary or not

begin

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history') –- Colleting the job names

--select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY') ---checks whether the instance is secondary or not

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog') –- Colleting the job names

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0' –- Disabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end