:::: MENU ::::

Posts Categorized / Security and Permissions

  • Mar 04 / 2009
  • 0
dbDigger, Logins and Users, Security and Permissions, SSMS tips and tricks

Disable log in of a user in SQL Server

It may be required to disable the account of a database user in various scenarios. In SQL Server 2000 we had no such option to disable a database user. From SQL Server 2005 and onwards there is an option to disable the log in of a database user.

  • Under security folder of your SQL Server instance, find and right click the user to whom you want to deny the login permission to SQL Server.
  • Right click the user and click properties
  • Following frame will be displayed, Go to status options

Deny LogIn for SQL Server User

Now click OK and the selected user is no more able to log in to SQL Server.

  • Mar 04 / 2009
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get permissions for SQL Server Database users

As a part of best practices implementation, some times i require to review and audit the rights of database users in a specific database. I have created following script to get list of permissions for all database users in a database. This script will work for SQL Server 2005 and onwards. I will run it on AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT USER_NAME(grantee_principal_id) AS [UserName],  
 OBJECT_NAME(major_id) AS ObjectName,  
 permission_name AS PermissionName,  
 state_desc AS PermissionStatus  
 FROM SYS.DATABASE_PERMISSIONS  
 WHERE class = 1  
 ORDER BY userName,permissionstatus  
 GO  

The result will be produced as shown a part in following snap.

Permissions for DB Users

It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.

  • Feb 25 / 2009
  • 0
SQLInjection
dbDigger, Security and Permissions

SQL Injection attacks compromised 500,000 sites in 2008

According to annual Web Hacking Incidents Database (WHID) report of Breach Security. Last year more than 500,000 sites were compromised through SQL Injection. The report wraps up with a breakdown of the sectors that suffered the most attacks on their systems. Government, security, and law enforcement take the top spot with thirty-two percent, followed by Information services, retail, Internet, and education rounding out the top five.
It is a threatening situation for IT systems and man power. Major IT software vendors should prefer the security upon ease of use and to some extent on performance. In recent decade the motivation was to deliver easy to use and easy to maintain products. But keeping in view the rising security threats it is need of hour to turn towards no compromise to security issues.
You can download the Web Hacking Incidents Database 2008: Annual Report Here

  • Jan 28 / 2009
  • 0
dbDigger, Logins and Users, Security and Permissions

Principals in SQL Server 2005,SQL Server 2008

From SQL Srever 2005, Microsoft has introduced the concept of principals. Principal may be a

  • Server login (Windows users, Windows security groups, SQL Server log in)
  • Database user
  • Role

Prior to SQL Server 2005 terms logins and users were being used. Now from SQL Server 2005 and onwards terms Server principals and database principals have been introduced.
Server principals include Server logins and roles
Database principals include database logins and roles

  • Jan 26 / 2009
  • 0
dbDigger, Security and Permissions, SQL Server Agent scheduled Jobs

Privilege ALTER TABLE / ALTER VIEW / ALTER INDEX may not be granted or revoked.

While implementing a planned new user for bulk updates, i got the failure report by that job. To investigate the reason when i scripted the job in SSMS query pane, i noticed that one of the stored procedures is generating the following error.
Privilege ALTER TABLE / ALTER VIEW / ALTER INDEX may not be granted or revoked.

It was a strange behavior because this stored procedure was updating a table whose update privileges were already granted to related user. When i examined the code i came to know that before starting updates in the procedure a statement was there to disable a trigger on that table. This ALTER TABLE statement was creating problem.
I was not willing to provide some extra permissions other than selected updates to job user. So how to get the job executed by providing minimal previlages to job user for altering table to disable the trigger.
For the solution i opened the properties frame of user in its related database folder.

SQL Server Granting Role

And assigned a database role “db_ddladmin” to it. It allows the user to use DDL statements like ALTER TABLE, ALTER VIEW and ALTER INDEX. Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

  • Jan 24 / 2009
  • 0
dbDigger, Logins and Users, Security and Permissions, SSMS tips and tricks

LogIn failed for user. The user is not associated with a trusted SQL Server connection.

While logging in to your SQL Server instance through SSMS/EM/QA, you may get following error message.
LogIn failed for user ”. The user is not associated with a trusted SQL Server connection.

LogIn Failed For User

In this case you have to change the authentication mode of your that SQL Server instance. Basically SQL Server can authenticate in two modes.

  • Windows authentication mode
  • Mixed mode authentication

If your instance is set to windows authentication mode then while trying to log in through SQL Server user, you will get above error message.
To remove the error and get log in through SQL Server ID, simply change the authentication mode of your SQL Server instance to mixed mode. Follow the following steps for it in SSMS.

Right click on your SQL Server instance and click properties

Go To Properties of Server Instance

A frame will appear with two authentication modes options on it. Change authentication mode to SQL Server and Windows Authentication mode (mixed mode) and click OK.

Set Mixed Mode Security

SQL Server instance restart will be suggested to implement the changes fully.

Configuration Change Confirmation

Restart your SQL Server instance by right clicking it and selecting restart menu item.

Restart SQL Server Instance Service

Now you can log in to this instance successfully. I logged in through SQL Server account id ‘test’.

Connected Through SQL Server Authentication

Consult us to explore the Databases. Contact us