| Author |
Topic |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2003-07-17 : 14:07:51
|
| Is there a simple way to saythis user cannot access this database?I know how to do it to each specific object, but is there a way to hit the whole enchilada at once?Thanks________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-17 : 14:15:49
|
| This will remove the account from the database:EXEC sp_revokedbaccess 'Corporate\GeorgeW'I don't think that it denies access though if say the user is an administrator on the server. I believe that the user will still have access. But how about you test it and let us know.Tara |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2003-07-17 : 14:26:02
|
| Well,Its gives me the User 'MY_USER' does not exist in the current database.What is strange is that I can log in as My_USER and then do selects on the Master database tables or Northwind. I was hoping there was a way where the My_User couldn't even know that they exist.... ________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-17 : 14:28:43
|
| Yes which means that MY_USER has access through a group or has server level access.Tara |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2003-07-17 : 15:13:44
|
| Well,the user has no server roles and I have no roles defined.I don't get it. <of course>________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-17 : 15:18:58
|
| But the user could have access to the server outside of SQL Server. The user could be a member of the administrators group on the machine.To track this down, check the administrators group on the machine. If the user is in there, then you aren't going to be able to prevent the access even if you deny permissions (the user can always un-deny the permissions). Then check if the user is a member of any of the server roles. If the user is a member of the system administrator role, then you aren't going to prevent the access due to the previous reason. Then check if the user has access to the database already. You also need to check what groups have been added to master. If the user is a member of any of these groups and one of these groups has access to the database, then the user will still have access. So what you need to do is isolate this user. But why not just deny the permissions and leave it at that. So what if the user can see that the database exists, the user still can't do anything in the database.Tara |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2003-07-17 : 15:28:43
|
Hmm,Well, the user isn't a part of any role that i can see anywhere, I guess I will just do a mass deny on the tables.Thanks for the info.quote:
But the user could have access to the server outside of SQL Server. The user could be a member of the administrators group on the machine.To track this down, check the administrators group on the machine. If the user is in there, then you aren't going to be able to prevent the access even if you deny permissions (the user can always un-deny the permissions). Then check if the user is a member of any of the server roles. If the user is a member of the system administrator role, then you aren't going to prevent the access due to the previous reason. Then check if the user has access to the database already. You also need to check what groups have been added to master. If the user is a member of any of these groups and one of these groups has access to the database, then the user will still have access. So what you need to do is isolate this user. But why not just deny the permissions and leave it at that. So what if the user can see that the database exists, the user still can't do anything in the database.Tara
________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-17 : 15:32:30
|
quote:
Well, the user isn't a part of any role that i can see anywhere, I guess I will just do a mass deny on the tables.
Just because the user isn't in any roles in SQL Server doesn't mean that the user isn't in a Windows group on the server.Tara |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-07-17 : 15:48:28
|
The reason they have access to the master and Northwind databases is because those databases contain the guest user. You can remove the guest user from Northind and pubs (or better yet just drop Northwind and pubs) but it must remain in master.From BOL: quote:
The guest user account allows a login without a user account to access a database. A login assumes the identity of the guest user when both of the following conditions are met: The login has access to an instance of Microsoft® SQL Server™ but does not have access to the database through his or her own user account.The database contains a guest user account. Permissions can be applied to the guest user as if it were any other user account. The guest user can be deleted and added to all databases except master and tempdb, where it must always exist. By default, a guest user account does not exist in newly created databases.
Jeff Banschbach, MCDBA |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2003-07-17 : 15:50:24
|
| Well,I created the account through sql server this morning.I don't have access to the file system, except through Sql Server. PerhapsSS can add roles to the windows accounts.I will figure it out someday, maybe.________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-17 : 15:55:59
|
quote:
Perhaps SS can add roles to the windows accounts.
By default, any user who is an administrator on the server will have system administrator privileges on SQL Server. Even if you can't change any accounts on the server, you can still view them. Just right click on My Computer and go to Manage. Then go to Local users and groups. You will need to check each group.You will need to do the above on the server. Every domain user or local user can log into the server, you just can't do much without permissions. If you don't want to do this, just have your server admin do it.TaraEdited by - tduggan on 07/17/2003 15:57:04 |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-07-17 : 15:59:06
|
| *thunk thunk thunk*Is this thing on?Jeff Banschbach, MCDBA |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2003-07-17 : 16:29:00
|
quote:
*thunk thunk thunk*Is this thing on?Jeff Banschbach, MCDBA
Well, the computer is, but I don't think I am anymore. I got too much done this morning, the mind has its own bandwidth throttler, now its thinking about consuming water mixed with barley and hops.Thanks for tips. Makes sense.________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-21 : 10:09:31
|
quote:
quote:
*thunk thunk thunk*Is this thing on?Jeff Banschbach, MCDBA
Well, the computer is, but I don't think I am anymore. I got too much done this morning, the mind has its own bandwidth throttler, now its thinking about consuming water mixed with barley and hops.Thanks for tips. Makes sense.________________________________________________(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick
No.I think Jeff means is...did you listen (read) his last post as to why whats happening?"Thank you! I'll be here all week!"Brett8-) |
|
|
|
|
deny, security