SQL Server Security | SqlContent

Identifying logins that have no passwords

Identifying logins that have no passwords SELECT name FROM sys.sql_logins WHERE PWDCOMPARE(”, password_hash) = 1 ;

Encrypting connections to SQL Server – SSL

The post is to demonstrate how to encrypt connection to SQL Server using SSL. At the bottom of the page you can see the video which demonstrates how to create a certificate and configure SQL Server to use SSL. SSL : Secure Sockets Layer is the most commonly used for web based client or native […]

Script out Server – Database Level Permission for all users and from all databases

This is always an issue that all the permission are gone when ever databases are refreshed from a backup.The below script will script Server ,Database,Object and Database Role Level Permission for all Databases and all users. Create a ServerList.txt Download the Script_Server_Database_Level_Permission.sql and Powershell Script and save it to local drive location. Replace the “Path” with correct […]

Enable / Disable Users In Database

I normally see this issue when connect permission is removed/revoked from the database USE DatabaseName GO GRANT CONNECT TO LoginName You can check which users in a database have the CONNECT permission by executing this T-SQL: USE DatabaseName GO SELECT name, hasdbaccess FROM sys.sysusers WHERE name = ‘LoginName’

The specified schema name “dbo” either does not exist or you do not have permission to use it.

use databasename GO GRANT ALTER ON SCHEMA::dbo TO [USER]

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’) go use master go select * from sys.server_principals where name =’test_replogin’ select * from sys.server_permissions where grantee_principal_id=270 go 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, […]

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 […]

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 […]

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’”. […]

Script to grant permission on database objects to user or role

Script :Grant Permisison on User Object to User (Read Only Permission) A Handy script to quickly script to grant permission on database objects to user or role go use databasename go –Select Permisison on User Table— DECLARE @login varchar(50) SET @login = ‘DBARoles’ DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500)) INSERT INTO @tables SELECT ‘GRANT […]