Archive for the ‘SQL Server Security’ Category

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

February 12, 2015 Leave a comment

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


Categories: SQL Server Security

Security Part : (Server Level Permission)

February 11, 2015 Leave a comment


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.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'test_replogin'


Categories: SQL Server Security

Understanding Shared Folders and Windows Firewall

December 4, 2014 Leave a comment

Understanding Shared Folders and the Windows Firewall


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.



139, 445


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.

Categories: SQL Server Security

Database user Permission and Server Role

November 14, 2014 Leave a comment

The script pulls user database and server level permission



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 when ''dbo'' then + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'' and owner_sid!=''sa'') + '')'' else 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 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 AS UserName, AS RoleName --,'EXEC sp_dropsrvrolemember '+QUOTENAME(,'''')+','+QUOTENAME(,'''') + ';', --'EXEC sp_addsrvrolemember '+QUOTENAME(,'''')+','+QUOTENAME(,'''') + ';' 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



Categories: SQL Server Security