:::: MENU ::::

Posts Categorized / Security and Permissions

  • Aug 19 / 2009
  • 0
dbDigger, Security and Permissions, SQL Server Agent scheduled Jobs

Where are fixed roles for SQLAgent?

While working in configuration of users and SQL Agent jobs, i was required to make one of my users, member of following fixed database roles.

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

These are fixed database roles and would be found only in msdb database. Although these fixed database roles work in server context rather than a database context but do not look for these s in fixed server roles. You may find these as fixed database roles of msdb database. Just right click user in users folder under security folder of server and go to properties

Where are fixed roles for SQLAgent

User should be mapped to msdb database and then make it member of required fixed database roles in msdb.
No user is member of these fixed database roles by default. To read further about capabilities of these fixed database roles, visit BOL.

  • Jul 11 / 2009
  • 0
dbDigger, Security and Permissions, SSMS tips and tricks

Grant any permission for all objects in a database through SSMS

Many times it is required to grant any permission for all objects in a database. Or we can say that it is required to grant a database level permission. Consider following tasks for examples

  • To assign EXECUTE permission on all stored procedures and functions to a user.
  • To assign SELECT permission for all tables and views in a database
  • To VIEW DEFINITIONS of all objects in a database
  • To UPDATE all tables and views in a database
  • Top INSERT data in any table or view
  • To DELETE data from any table or view

All these permissions may easily be granted through SQL Server Management Studio (SSMS). It is notable that object level permissions will over ride these database level permissions.
Suppose we have to grant select permissions on all tables and views to User1

  • Open SSMS
  • Right click on database folder on which permissions are required to grant
  • Click properties
  • Click permissions in left panel of frame and also select the user to which permissions are to be granted

Grant database level permissions through SSMS

Here in lower panel you will find about 60 different permissions. Most of these are with self explanatory names. As we are required to grant select permission here on all tables and views to User1. So Click grant for Select and click OK. Task is over and now User1 can select data from any table or view.
If we deny SELECT for any individual table to User1 then deny on object level will have precedence on database level permissions and User1 will not be able to select data from that restricted object.
So you may go through all permissions provided here and use them according to scenario.

  • Jun 30 / 2009
  • 0
dbDigger, Security and Permissions

The CREATE SCHEMA statement should be followed by a name or authorization keyword.

The Error Message:
Msg 4111, Level 15, State 1, Line 1
The CREATE SCHEMA statement should be followed by a name or authorization keyword.

is a run time error message introduced in SQL Server 2005. This error message appears when you try to create a new schema with CREATE SCHEMA without specifying anything else.
While creating a new schema you must specify either a schema name or an authorization keyword right after the CREATE SCHEMA command.

Example of such error generated is as follows

USE tempdb;
GO
CREATE SCHEMA
  • Jun 26 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

How to disable or enable SQL Server logon trigger

In my previous post we discussed process to implement logon trigger for different purposes. Later some time it may be required to skip the customizations implemented through logon trigger. So for this you may disable the logon trigger rather than to drop it.
If we want to disable the trigger [RestrictSSMSLogIn] that we created during previous post, following is T-SQL command to disable the logon trigger

 
DISABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  

You may use following T-SQL command to enable the logon trigger later at any time

 
ENABLE TRIGGER RestrictSSMSLogin  
 ON ALL SERVER   
 GO  
  • Jun 25 / 2009
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict SQL Server login to connect from out side the application through logon trigger

There is a situation that I have a SQL Server login with rich rights in some of databases. It is used for connection through a .Net application. It is not assigned to any one for use in SSMS etc. I want to make sure that this login would not be used for connection to SQL Server out side the application. While looking at various possible options i ca,e to know LogOn triggers in SQL Server.
Logon triggers in SQL Server are used to carry out logon related customization and tasks. From onwards to SQL Server 2005 SP2, we can create logon triggers that are meant to be fired every time when a session is established to SQL Server. If a login is not successfully authenticated then logon trigger is not fired in that case.
Consider following example to implement the above requirement. We will restrict the user AppUser to connect to SQL Server through .Net application only.

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 (SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

Trigger has been created and user ‘AppUser’ is no more able to connect to SQL Server through any means other than .Net application.
Logic implemented in if block may be modified to implement a large number of other customizations that are required.
Logon triggers are created in master database. And may be viewed in SSMS in server objects folder.

LogOn Trigger in SSMS

While trying to log in SSMS through logon trigger following message is generated, showing that created logion trigger is working fine.

LogOn Restricted through LogOn trigger

It is important to mention that be attentive while working with logon trigger, some wrong or missing conditions may block all SQL Server logins to log in to SQL Server. Best solution is that be logged in through any user in SSMS while testing logon trigger. If any problem occurs due to trigger you can use already logged in user to manage the situation.
Even if you do not have a log in connected and a problem occurs due to logon trigger then work according to this article.
Click here
to get T-SQL commands to enable or disable the SQL Server logon trigger

  • Jun 05 / 2009
  • 0
dbDigger, Security and Permissions

The MUST_CHANGE option is not supported by this version of Microsoft Windows.

The error message
Msg 15195, Level 16, State 1, Line 2
The MUST_CHANGE option is not supported by this version of Microsoft Windows.
is a run time error message introduced in SQL Server 2005. It appears when you try to create a login with SQL Server authentication that must change its password upon first connect, but the operating system running SQL Server does not support this setting. XP does not support change password option for first login. So you may encounter this error on XP. So remove MUST_CHANGE option from statement on such OS.

Consult us to explore the Databases. Contact us