Security Part : (How to transfer logins and passwords between instances of SQL Server)

This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005,of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers.

Microsoft SQL Server provided sp_help_revlogin stored procedure which will ease this

use master
go
exec sp_help_revlogin

Security Part : (Server Level Permission)

 

Get list of built-in securable classes

SELECT * FROM fn_builtin_permissions(default)

use master
go
select * from fn_my_permissions('test_replogin','USER')  

use master
go
select * from sys.server_principals where name ='test_replogin'
select * from sys.server_permissions where grantee_principal_id=270

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pe.state_desc, pe.permission_name 
FROM sys.server_principals AS pr 
JOIN sys.server_permissions AS pe 
    ON pe.grantee_principal_id = pr.principal_id where pr.name='test_replogin'

Understanding Shared Folders and Windows Firewall

Understanding Shared Folders and the Windows Firewall

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

 

Sharing a folder or file creates a Windows Firewall exception for File and Printer Sharing. The exception opens the ports listed in the following table.

Connection
Ports

TCP

139, 445

UDP

137, 138

The default scope is to allow access from any computer on the network, including computers on the Internet. Unless you block incoming connections on these ports using a hardware firewall, firewall server, or other Internet-sharing device, your computer will be vulnerable to attack from the Internet as long as your Internet connection is active.

Database user Permission and Server Role

The script pulls user database and server level permission

 

Script

DECLARE @DB_USers TABLE 
(DBName sysname, UserName sysname, LoginType sysname,
AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers 
EXEC sp_MSforeachdb 
' 
use [?] 
SELECT ''?'' AS DB_Name, 
case prin.name when ''dbo'' then prin.name +

'' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'' and owner_sid!=''sa'') + '')'' else prin.name

end AS UserName, 
prin.type_desc AS LoginType, 
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date 
FROM sys.database_principals prin 
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id 
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and prin.type_desc!=''DATABASE_ROLE'' and 
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%''' 

SELECT t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user DatabaseRole, 
t2.RoleName ServerRole 
FROM (SELECT  dbname,username ,logintype ,create_date ,modify_date , 
STUFF((SELECT ',' + CONVERT(VARCHAR(500),associatedrole) 
FROM @DB_USers user2 
WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName 
FOR XML PATH('') 
),1,1,'') AS Permissions_user 
FROM @DB_USers user1) t1
left JOIN 
(SELECT Logins.name AS UserName, Roles.name AS RoleName 
    --,'EXEC sp_dropsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';', 
    --'EXEC sp_addsrvrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Logins.name,'''') + ';' 
FROM sys.server_role_members RoleMembers 
JOIN sys.server_principals Logins 
    ON RoleMembers.member_principal_id = Logins.principal_id 
JOIN sys.server_principals Roles 
    ON RoleMembers.role_principal_id = Roles.principal_id 
) t2 
on t1.UserName=t2.UserName   
Group by t1.dbname,t1.username ,t1.logintype ,t1.create_date ,t1.modify_date,t1.Permissions_user, 
t2.RoleName

 

Troubleshooting specific Login Failed error messages

We have faced these issues several times with many reasons like Cluster issues, network problems, Domain issues, Login Transfer Issues.

But apart from all these issues there are some uncommon issues as well, one of which i am explain below.

I came across one this issue recently

Issue:
User X is not able to login to SQL Server (application or SSMS).The user was able to login to SQL Server before the issue started occurring from … Date.

Logon Error: 18456,Severity: 14, State: 11.

Logon Login failed for user
<Server name>\Name’. Reason: Token-based server access validation failed
with an infrastructure error. Check for previous errors. [CLIENT:]

Login Failed for User <ServerName>\Username’?

Reason:

We have added a User from AD (Windows Integrated) to SQL Server as Login, Later due to some reason we have to drop that user from the AD and created it again. But we did not drop the user from SQL Server.

The SID of the user has changed on the AD whereas SQL Server still has the old SID which invoked the error.

Login Error State 11 means “Valid login but server access failure”, which further points that SQL Server Login is valid but missing certain security privileges which would grant access to the instance.

Resolution

1. Look into the SQL Error log and verify that that the login failed message for the user has a State 11. You can alternatively verify the state number from the SQL Server default traces as well (available from SQL Server 2005 and above).

2. Next look into the Ring Buffers output and find out what was the API that failed.

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, dateadd (ms, (a.[Record Time] – sys.ms_ticks), GETDATE()) as [Notification_Time], a.* , sys.ms_ticks AS [Current Time] FROM (SELECT x.value(‘(//Record/Error/ErrorCode)[1]’, ‘varchar(30)’) AS [ErrorCode], x.value(‘(//Record/Error/CallingAPIName)[1]’, ‘varchar(255)’) AS [CallingAPIName], x.value(‘(//Record/Error/APIName)[1]’, ‘varchar(255)’) AS [APIName], x.value(‘(//Record/Error/SPID)[1]’, ‘int’) AS [SPID], x.value(‘(//Record/@id)[1]’, ‘bigint’) AS [Record Id], x.value(‘(//Record/@type)[1]’, ‘varchar(30)’) AS [Type], x.value(‘(//Record/@time)[1]’, ‘bigint’) AS [Record Time] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = ‘RING_BUFFER_SECURITY_ERROR’ ) AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys ORDER BY a.[Record Time] ASC Output of the query  in Case of Error will be as below Calling API Name: NLShimImpersonate API Name: ImpersonateSecurityContext Error Code: 0x139F (The group or resource is not in the correct state to perform the requested operation)

3. Check if that login is directly mapped to one of the SQL Server logins by looking into the output of sys.server_principals.

4. If the login is directly mapped to the list of available logins in the SQL instance, then check if the SID of the login matches the SID of the
Windows Login.

If the result in setup 2 is as mentioned drop the SQL Server login and create it again.

Try to fix the Orphaned User. Search in BOL Orphaned User

Drop Failed for User

It’s a very common issue which every DBA faces in there daily administrative activities. When we restore a database along with it few users comes along, which own’s permission on database objects.  These user’s are orphaned which need to be deleted.

While deleting these user we faces a very common error

“DROP LOGIN FAILED FOR USER ‘XYZ'”.

The database principal has granted or denied permissions to objects in the database and cannot be dropped.

To resolve these kind of  issue we struggles a lot and spend our precious time in these type of activities.

Here we will see how to over  come these issues without spending much time.

Once the database is restored check for Orphaned Users

USE <Databasename>;
GO
sp_change_users_login @Action=’Report’;
GO

Once Users are identified we will figure out what permission they hold in the database

1).select permission_name,state_desc,object_name(major_id) as securable,

user_name(grantor_principal_id) as grantor

from sys.database_permissions

where grantee_principal_id = user_id(‘XYZ’)

2.)select * from sys.database_permissions

where grantor_principal_id = user_id (‘XYZ’);

 

Once user permission are identified revoke the permission.

REVOKE the defined permission from step1.

REVOKE the impersonate permission from user ‘XYZ’ .

What is Impersonation?

SQL Server supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement, or implicitly by using the EXECUTE AS clause on modules. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

Implicit impersonations that are performed through the EXECUTE AS clause on modules impersonate the specified user or login at the database or server level. This impersonation depends on whether the module is a database-level module, such as a stored procedure or function, or a server-level module, such as a server-level trigger.

REVOKE IMPERSONATE ON USER::labelsecurity TO public

The above statement will execute successfully, but still not able to drop the user same error as mentioned above.

Execute the Query in step 2 , get the granter_principal_id then pass the same in below query to get the user name who impersonated the user.

SELECT USER_NAME(granter_principal_id)

use [Database_Name]
GO
REVOKE IMPERSONATE ON USER::[XYZ] TO [RU] AS [XYZ]
GO

Now try dropping user again.