:::: MENU ::::

Posts Categorized / Security and Permissions

  • Feb 16 / 2012
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

  1. Login is requesting to connect from inside the application code
  2. Login is requesting from no where else but web server (application server)

My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 DECLARE @ip VARCHAR(16)  
 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 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'  
 or @ip <> '192.168.1.202') -- Provide IP address of web server here  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

In this way the login used for application is restricted to access only from web server through application code only.

  • Mar 17 / 2011
  • 1
dbDigger, Security and Permissions, T-SQL Scripts

Generate permissions for a login in all databases

In one of my previous posts i shared a script to generate all permissions for a database. Couple of days back a blog reader commented on post and asked for a script that may generate permissions for a specific login in all databases. Such script would be helpful for servers with many databases. I have tried to accomplish the task by combining the previously posted script with some other procedures.
Whole script is composed of 6 steps/modules. Let us have a look at 6 steps that together build whole script.

  1. Create a procedure (USP_GeneratePerm) to generate permissions
  2. Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases
  3. Execute USP_CreateInAllDBs with sp_MSForEachDB
  4. Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases
  5. Execute USP_ExecuteInAll
  6. Housekeeping

As we can see in above steps that three stored procedures are created and executed.  Last step would be to drop the created procedures.

  • Prepare your mind that this script may need some modifications according to your scenario. Especially the statements where system/READ ONLY databases are need to excluded from the databases in which our sp would be executed.
  • Script would not be generated for READ ONLY databases. So for READ ONLY databases permissions may be generated separately.
  • Rather than to execute whole script at a time. Execute each of six steps isolated.
Now we would go through the brief description of above mentioned 6 steps

Create a procedure (USP_GeneratePerm) to generate permissions

A stored procedure would be created in master database. Procedure USP_GeneratePerm would be accept two parameters for database name and login. It would actually generate the permission statements.

Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases

The procedure USP_generatePerm is required to be created in all databases other than system and READ ONLY databases. So to complete this requirement we have to create another procedure USP_Create InAllDBs. It would also except two parameters. One is name of database where primary procedure would be created and second is name of primary procedure. In our case primary procedure is  USP_GeneratePerm.

 Execute USP_CreateInAllDBs with sp_MSForEachDB

In third step we would execute USP_CreateInAllDBs in all database by using sp_MSForEachDB system stored procedure. I have exclude the system databases in script.

Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases

No we have our primary SP created in all databases other than ssytem and READ ONLY databases. Now it is required to execute the primary SP in all databases. For this purpose we would create another SP named USP_ExecuteInAll. It would accept one parameter that is login for which script is required to be generated.

Execute USP_ExecuteInAll

Execute the above created USP_ExecuteInAll by providing login as parameter.

Housekeeping

Drop the procedures created in mater and other databases.
According to above descriptions, it is obvious that login for which permissions are required to be generated is provided in step # 5 i.e. Execute USP_ExecuteInAll.
Click here to download the script file. If any one could help to further improve the script and reduce its complexity then you are welcome with due credit on this blog.

  • Jun 09 / 2010
  • 0
Backup and Restore, dbDigger, Encryption, Security and Permissions, T-SQL Scripts

The backup set holds a backup of a database other than the existing ‘*’ database.

Recently i applied password protected backups for my SQL Server databases as explained in my article Using passwords with SQL Server database backup files. Backups and file removal task worked successfully in test environment. Before moving the encrypted backups setup to production systems i just performed a hands on activity to be smooth with restore process of password protected backups.
I created password protected full backup of AdventureWorks with following script to work with restore scenarios.

After creating backup successfully in ‘D:Testing’ directory, i planned it to restore over an existing database AdvTest.

 

It is notable that password protected backups may not be restored through SSMS, so we have to use T-SQL scripts for this purpose.

I got password used for backup by following select statement

select replace(convert(varchar, getdate(), 102),'.','-')+'-MSSQLTips'
GO

Using following script, i tried to restore AdventureWorks backup on existing AdvTest database

Use master
GO
RESTORE DATABASE AdvTest
FROM DISK='D:TestingAdventureWorks-2010-05-31 15-04-53.bak'
WITH MEDIAPASSWORD='2010-05-31-MSSQLTips'
GO

Restoration of password protected backup failed on existing database failed with following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdvTest’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

After some research on issue i came to know a work around. It is required to use WITH REPLACE option for restore of backup on another database with different name. So i modified my script as

Use master
GO

RESTORE DATABASE AdvTest
FROM DISK='<span style="color: #3333ff; font-family: courier new; font-size: small;">D:TestingAdventureWorks-2010-05-31 15-04-53.bak</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">'
WITH REPLACE, MEDIAPASSWORD='</span><span style="color: #3333ff; font-family: courier new; font-size: small;">2010-05-31-MSSQLTips</span><span style="color: blue; font-family: 'Courier New',Courier,monospace; font-size: small;">',
MOVE 'AdventureWorks_data' TO 'D:DB FilesAdvTest.mdf',
MOVE 'AdventureWorks_log' TO 'D:DB FilesAdvTest_log.ldf'

Here i have specified the data and log files of source and target. It is important to note that if database is being restored on same database to which it belongs then move option is not required to be specified.
For example if we are required to restore our AdventureWorks backup on AdventureWorks database then following script would work

Use master
GO

RESTORE DATABASE AdventureWorks
FROM DISK=’D:TestingAdventureWorks-2010-05-31 15-04-53.bak‘
WITH REPLACE, MEDIAPASSWORD=’2010-05-31-MSSQLTips‘,
GO

To get logical and physical file names of any database for use in above scripts, use following command

Use AdvTest
GO

sp_helpfile
GO
  • Apr 28 / 2010
  • 0
Changing passwords for logins used with SQL Server maintenance plans
Backup and Restore, dbDigger, Encryption, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Configure SQL server password protected backups creation and cleanup

Securing the backup and data files is more important than to establish optimal rights and privileges for database users. SQL Server backup file contains data and log files and it can easily be restored once in hand. It is critical to device a mechanism where backup file should be safe at disk and also another layer of protection is required in backup file itself.
Several tools can be found for backup file encryption, but i have discussed an SQL Server facility to create password protected backup files. It may be used with SQL Server 2000/2005/2008. You can read full article on this topic here.
There are two considerations in this regard

  • The applied password encryption on backup file would not be as reliable as in strongly encrypted files
  • This feature may be removed in future versions of SQL Server, so use it till SQL Server 2008 only
  • Apr 01 / 2010
  • 0
dbDigger, Maintenance plans, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Changing passwords for logins used with SQL Server maintenance plans

SQL Server maintenance plans are widely used for automating various database tasks. Maintenace plans uses the login for tasks that was used to create maintenance plan. Changing the password of that specific login would cause problems in maintenance plan while loging in to perform tasks. To get solution for this problem you look my following article at mssqltips.com
Changing passwords for logins used with SQL Server maintenance plans

  • Sep 01 / 2009
  • 0
dbDigger, Encryption, Publications of Atif Shehzad on MSSQLTips.com, Security and Permissions

Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission

My article related to Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Options for hiding SQL Server code through WITH ENCRYPTION clause and VIEW DEFINITION permission. It compares both methods (using WITH ENCRYPTION clause and VIEW DEFINITION permission)to hide SQL Server objects code.

Consult us to explore the Databases. Contact us