:::: MENU ::::

Posts Categorized / Security and Permissions

  • Nov 26 / 2008
  • 0
dbDigger, Security and Permissions, System Administration

password policies for SQL Server login for Windows Server 2003

On a computer running Windows Server 2003 or later hosting a SQL Server instance, which of the following password policies are checked for a SQL Server login when CHECK_POLICY is ON and CHECK_EXPIRATION is OFF?

  • Enforce password history
  • Minimum password age
  • Minimum password length
  • Password must meet complexity requirements

There is nothing written specifically indicating which policies are checked by CHECK_POLICY. The answer was determined by experimentation (SQL Server 2005 SP2 on Vista). A SQL Server login was created, then a query window was opened with the connection made using that login. ALTER LOGIN WITH PASSWORD = ” OLD_PASSWORD = ” was run when various policies were applied.

Password policy – http://msdn.microsoft.com/en-us/library/ms182717.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, Security and Permissions, T-SQL Scripts

Database roles for SQL Server databases

A login may be member of a Server role or database role. Member of database roles have permissions for that specific SQL Server database. Following script will show that which SQL Server login is member of a particular database role.

 
USE DBName  
 GO  
 SELECT  
 p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role  
 FROM sys.database_role_members roles  
 JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id  
 JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Server roles for SQL Server

A login may be member of a Server role or database role. Member of server roles have permissions for SQL Server instance level tasks.Following script will show that which SQL Server login is member of a particular server role.

select p.name, p.type_desc, pp.name, pp.type_desc  
 from sys.server_role_members roles  
 join sys.server_principals p on roles.member_principal_id = p.principal_id  
 join sys.server_principals pp on roles.role_principal_id = pp.principal_id  
 GO  
  • Nov 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Who has access to your database

Once a user is logged in to your SQL Server instance, he may or may not have access to your databases. In order to access a database, SQL Server login of that user must be mapped to database user created inside the database. Use following command to list the logins who have access to your databases. I have used sys.database_principals system view for this information.

 
use DBName  
 GO  
 SELECT UserName = dp.name, UserType = dp.type_desc,  
 LoginName = sp.name, LoginType = sp.type_desc  
 FROM sys.database_principals dp  
 JOIN sys.server_principals sp ON dp.principal_id = sp.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.

Consult us to explore the Databases. Contact us