November 3, 2008 at 12:33 am
My company makes a dbm app that manages the sql server 2005 database that serves a client app. This database manager app prompts for windows or sql server authentication to connect to the sql server.
The sql server may host more than one of these database. The dbm app shows a list of these databases.
For the dbm app to work, the login must have the sysadmin permissions on the server . This is hard coded into the dbm app. If a login has no sysadmin, the app refuses to connect.
We are implementing this for an umbrella organisation that hosts one database for each of it's member organisations The objective is this...
a) a user from each of these member organisations will connect to a terminal server and use this dbm app to take backups of there own database on an adhoc basis.
b) they must not be able to take a backup of anyone elses database other then their own.
Problem - for the dbm app to work, their sql server login must have the sysadmin role, which gives them power-of-god over all other databases including their own. How can I restrict the permissions of such a user for individual databases ?
As I understand it, aNy sysadmin login is by implication linked to the dbo user in each database, and we can't drop the dbo database user or deny any of its roles, without getting the error "cannot use the special principal dbo" .
What can I do ?
November 3, 2008 at 3:13 am
Hello,
If the DBM Application really must have the Sysadmin Server Role then you are in trouble.
The only glimmer of hope that I can think of, is that in general a Deny overrides an implicit or explicit Grant. So you could try as an experiment explicitly denying permissions to the DBM’s Login. That would need to include the ability for it to Grant permissions i.e. back to itself. I suspect this denial of grant permissions won’t work for the Sysadmin Role, but it’s probably your only hope. Even if it doesn’t then at least you would have slightly improved security i.e. having seen an access denied message, the users would have to realise that they could grant permissions back to themselves.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 3, 2008 at 9:30 am
mduffy (11/3/2008)
Problem - for the dbm app to work, their sql server login must have the sysadmin role, which gives them power-of-god over all other databases including their own. How can I restrict the permissions of such a user for individual databases ?
You can't. You cannot deny any rights to a sysadmin, you cannot limit a sysadmin. The sysadmin role means that all security checks are bypassed.
The only things I can suggest are to install the dbm app on a separate server, or to speak to the vendor and persuade them to change the app.
Since you're on 2005, you could also implement DDL triggers to prevent the dbm login from doing certain operations. It's not going to help with the backups, and it is possible for the users to drop the triggers (if they figure out that they are there)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply