I - To create a login with access to specific database only:
- Use SSMS and connect to the SQL server instance
- Open Security -> right click Logins -> New Login (e.g. login named SE)
3 Enter the login details. In Server Roles, make sure only 'public' is checked
4 In User Mapping, make sure only the vendor database is selected.
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
T-SQL statement to deny creating backups is:
DENY BACKUP ANY DATABASE to [SE]
At the database level, you could simply add both to the same database role. Of course, when you create the role, you'll have to grant it all the same permissions as the existing group, but you only have to do that once.
At the server level, to do that without re-applying all of the permissions (which you could probably script without a whole lot of effort - you can get some ideas here), you'll have to wait for SQL Server 2012, unless you can use a fixed server role.
Best Answer
Removing a principal from
sysadmin
role does not remove the principal frompublic
role. You cannot usedeny
on members ofsysadmin
role or the object owners. Usingsp_dropsrvrolemember
system stored procedure to remove the principal fromsysadmin
role should be your solution. If somebody does not belong to the party, better kick him out. Remember you need to besysadmin
yourself if you want to remove another principal fromsysadmin
role.