:::: MENU ::::

Posts Categorized / Logins and Users

  • 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.

  • 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 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

  • Nov 12 / 2008
  • 0
DBA Interview questions, dbDigger, Logins and Users, SQL Server tools

Change password with -Z parameter through SQLSMD

Question: You are trying to run some scripts using SQLCMD but keep getting errors that your password has expired. SSMS is not installed on this machine. What should you do?

Answer: Use the -Z parameter to change your password.

Explanation: While many of us might feel like going home, that’s not a great solution. Either calling a DBA or finding another workstation will work, but that’s inefficient. With the -Z parameter, you can change your own password with SQLCMD.

Ref: SQLCMD – http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx

Note: The Question is taken from SQLServercentral.com.

  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

List permissions for logins in SQL Server database

It is very important information that which user has which rights and permissions in your SQL Server databases. By this way you may periodically monitor the permissions and chances for any accidental hidden loop hole will be minimized. Following script will provide you the permissions of logins in a database.

 
USE DBName  
 GO  
 SELECT  
 dp.class_desc, dp.permission_name, dp.state_desc,  
 ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id  
 JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who can logIn to your SQL Server instance

Being a DBA you would like to have information related to access at various levels in SQL Server. Primarily the question is that who can login to your SQL Server instance. Use following command to get list of all log ins who are able/disable to log in to your SQL Server instance. Command will query sys.server_principals system view and will get information about windows logins and SQL Server logins.

SELECT name, type_desc, is_disabled
FROM sys.server_principals

Result will be in following form

Access to SQL Server instance

Also you may apply filter on ‘isdisabled‘ and ‘type_desc‘. In above result set login name sa is disabled so it can not be used for login to SQL Server instance.

Pages:1234
Consult us to explore the Databases. Contact us