Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have sql server 2005, I need to create separate database login to support engineer of my software vendor to access their own database. My only worry is deny him to take backup from our database while he connected remotely to resolve any incident in the application.

To sum up,

1- How to create login with access to specific database only 2- How to monitor his activity on database (what query he runs, is he try to took any backup and when) 3-Deny Any backup and restore task (I want to keep this only to sysadmin)

share|improve this question
add comment

migrated from stackoverflow.com Dec 17 '12 at 5:32

This question came from our site for professional and enthusiast programmers.

1 Answer

I - To create a login with access to specific database only:

  1. Use SSMS and connect to the SQL server instance
  2. Open Security -> right click Logins -> New Login (e.g. login named SE)

enter image description here

3 Enter the login details. In Server Roles, make sure only 'public' is checked

enter image description here

4 In User Mapping, make sure only the vendor database is selected. enter image description here

If public role for the vendor database is not enough, grant the necessary permissions but only on the vendor database

Another solution is to deny access to all databases and then grant just to the vendor database

DENY VIEW ANY DATABASE TO [SE]

See more info here: http://msdn.microsoft.com/en-us/library/ms177518.aspx

II- How to monitor his activity on database (what query he runs, is he try to took any backup and when)

I suggest using some third party tools. Transaction logs don't capture info about executed queries

III-Deny Any backup and restore task (I want to keep this only to sysadmin)

Having public role will prevent the user to backup or restore databases

he'll get the following messages when he tries to backup or restore a database

enter image description here

enter image description here

T-SQL statement to deny creating backups is:

DENY BACKUP ANY DATABASE to [SE]

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.