SQL Server Performance Forum – Threads Archive
Deny Database Access
Working with SQL Server 2k, I’m trying to learn the best way to set up permissions. I’ve created a number of Logins – Windows NT and SQL Server logins – and am testing access by logging into QA with the sql login, etc. Anyway, I was surprised that — out of the box — the accounts have full to every database. I have explicitly gone into the login permissions and selected permit on the databases (master, msdb, etc) so I could select Denydatareader & write. This denied access as did DENY ALL. Here are some questions I hope somewill can help me with… Is the above (denydatareader/writer or Deny ALL the correct approach?
Is there a T-SQL stmt that can deny users access to entire database (master, msdb..)?
I tried to use DENY ALL TO DOMAINUsername and got a message that no such user was found even though the user exists as a login. Does anyone know what the problem is?
I’m thinking about deleting the BUILTINADMINSTRATORS account and creating a new NT group account for SQL Admin(s) only in it’s place — for tighter control. Would this have any adverse effects I might have overlooked? Thank you for any help you can give.
Ekl
I see no one answered so far so I’ll try. When you create a login and give it no permissions but the default, it will not have access to any database unless this database has the guest account. Remove the guest account from databases other than master and tempdb (you need them in master otherwise you cannot login).
At this point when you log with the user in QA, you should not be able to see database other then
the 2 above. You can see the objects in the databases by default (probably because of the guest account), which I find strange, but you cannot do much.
I thought about deny select on master..sysobjects to user_name, but you would have to add user_name to master to deny permissions and that is probably something you want to avoid.
I don’t know if you can deny something on guest account to prevent that without causing problems…
Hopfully someone more experienced will be able to come up with a solution. Bambola.
DENY ALL revokes all the permissions from the user and not just reading and writing data. So if your user has some other permission like BACKUP etc. those will be revoked by DENY ALL. Did you try using [DomainAccttName] syntax as suggested in BOL for removing access? As a good security practice, some people recommend removing the BUILTINAdministrators account and create a new account for SQL Administrators. In this case make sure that you add the loacal administrator login to the newly created group. If you don’t want to do that make sure you don’t lose your sa password. Failing to comply with either of these may lead to a situation where you lose sa password and you can’t get into machine using system admin privilages. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Its better to create role for this processing and allocate appropriate permission in the database. If the user numbers are less then better to assign directly to the user. If the current security is working on ROLES and permissions then follow the same procedure to accomplish.
And for information refer thru this link http://www.sql-server-performance.com/vk_sql_security.asp.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
quote:Originally posted by bambola
I see no one answered so far so I’ll try. When you create a login and give it no permissions but the default, it will not have access to any database unless this database has the guest account. Remove the guest account from databases other than master and tempdb (you need them in master otherwise you cannot login).
At this point when you log with the user in QA, you should not be able to see database other then
the 2 above. You can see the objects in the databases by default (probably because of the guest account), which I find strange, but you cannot do much.
I thought about deny select on master..sysobjects to user_name, but you would have to add user_name to master to deny permissions and that is probably something you want to avoid.
I don’t know if you can deny something on guest account to prevent that without causing problems…
Hopfully someone more experienced will be able to come up with a solution. Bambola.
At this point when you log with the user in QA, you should not be able to see database other then
the 2 above. You can see the objects in the databases by default (probably because of the guest account), which I find strange, but you cannot do much.
I thought about deny select on master..sysobjects to user_name, but you would have to add user_name to master to deny permissions and that is probably something you want to avoid.
I don’t know if you can deny something on guest account to prevent that without causing problems…
Hopfully someone more experienced will be able to come up with a solution. Bambola.
Got several good replies. This is a wonderful forum — very grateful. You are right about login not having access by default — i.e. unless the user has Admin privileges which is the case on the users showing up with access all over the place. I have added a localAdministrator account and will be deleting the BuiltinAdministrators entry. On the test server I get Error 21776:[SQL-DMO] The name ‘dbo’ was not found in the Users
collection. If this is a qualified name, use [] to separate various parts of the name, and
try again. Seems a common error because I found information on it on a number of different sites. I used sp_changedbowner and set the owner to ‘sa’ on all db’s. I have no mismatched users/logins but I still get the error. It doesn’t seem to be causing a problem but I would love to fix it regardless. Are you aware of another fix or possible cause that might address it? I will look at setting up roles… any way of tying a role to running QA? I have a couple of SQL savvy users that run their own ad hoc queries in QA but I would like to restrict their access to read only via QA but Select, Update, delete through the application. I tried setting up an application role in the vendor’s application but it wouldn’t support calling the stored proc. Thank you again. It’s been very helpful.
quote:Originally posted by bambola
I see no one answered so far so I’ll try. When you create a login and give it no permissions but the default, it will not have access to any database unless this database has the guest account. Remove the guest account from databases other than master and tempdb (you need them in master otherwise you cannot login).
At this point when you log with the user in QA, you should not be able to see database other then
the 2 above. You can see the objects in the databases by default (probably because of the guest account), which I find strange, but you cannot do much.
I thought about deny select on master..sysobjects to user_name, but you would have to add user_name to master to deny permissions and that is probably something you want to avoid.
I don’t know if you can deny something on guest account to prevent that without causing problems…
Hopfully someone more experienced will be able to come up with a solution. Bambola.
At this point when you log with the user in QA, you should not be able to see database other then
the 2 above. You can see the objects in the databases by default (probably because of the guest account), which I find strange, but you cannot do much.
I thought about deny select on master..sysobjects to user_name, but you would have to add user_name to master to deny permissions and that is probably something you want to avoid.
I don’t know if you can deny something on guest account to prevent that without causing problems…
Hopfully someone more experienced will be able to come up with a solution. Bambola.
To answer your last question. You should grant them execute permission on the stored procedure they can run.
GRANT EXECUTE ON stored_procedure_name TO login_name
Once you do they will be able to run the stored procedure connecting to QA with login_name. Bambola.
Question 1:
Make sure that you run exec sp_changedbowner ‘sa’ on all databases. Doesn’t seem so if you still get the error message. If you haven’t closed enterprise manager and re-opnened it after making the changes do that as well. Question 2:
Create a new role in the database. Give this role db_datareader access. Then add all users to this role (be it SQL or NT logins). Note that it won’t help if the users know the actual SQL login that your application might use. You want to change the password if that is the case. /Argyle
I also want a singele user or maybe 2, to deny access to a certain database. Only the problem is, that the crm-software that i’m using has some kind of account that can always access the database as well as it’s users. Is there a way to deny access for a windows account, no mather with what software you’re accessing the db? Greetz,
Diablo
]]>