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.