Nightly denial of db access to standard logins
-
I need to lock standard users out of my SQL2K servers on a nightly basis so that I can perform loads. I need to give them back the same permissions they had in the morning. There are hundreds of tables so denying at the object level is not an option. With the Grant All option it sounds like it will grant all available permissions not just the ones they had before the deny. These users are already in a group that allows select access. Any help would be great appreciated.
-
You could try killing all the users when needed and putting the db in single user mode. But can be dangerous killing all the users. Or also could stop and restart the SQL server service in single user mode too
-
Create a new role that deny's access to all tables.
Add the user role to this role at night
run load
remove user role from this role
Steve Jones
-
Thanks. I could probably add and remove the user role from the db_denydatareader role, but I was hoping for something slick like sp_denylogin/sp_grantlogin for standard database logins, but it looks like that isn't available.
-
As racosta said, killing connections could be dangerous.
Here is another solution.
If you pause SQL, it waits for all connections to close and denies new connections.
You could, using xp_cmdshell, pause it buy running "net pause mssqlserver"
No new connections will be allowed (Yours will still be active) run your script and again execute "net continue mssqlserver". This'll bring SQL back to normal.
Have a look in BOL for "pause". Quite a bit about it.
quote:
When you pause an instance of Microsoft® SQL Server™, users who are connected to the server can finish tasks, but new connections are not allowed. For example, you can pause an instance of SQL Server for a few minutes and send a shutdown message to connected users before shutting it down. You can also resume a SQL Server service.Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks Crispin. Alas, it's an automated load and pausing the server might be a hit and miss thing and the user I have doing the loads has minimal priv's so I can't do the dbo/admin only setting. Does bring up one question that I haven't tested yet. What happens when I deny rights to a user that is already connected? Do I need to kill processes to assure that no one is querying the server?
-
Something else I found in BOL. Look at xp_cmdshell.
quote:
CREATE PROC shutdown10AS
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down
in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I did something a couple years ago similiar to what you are after. (I Think
)What I did was get a list of users (sp_who) and attempted to kill each one. As you cannot kill your own, this is good.
I have just been reading in BOL about kill (I had not done so at the time
) and a kill will force a rollback. You can also, if needed, get status of the rollback. KILL 54 WITH STATUSONLY
Not to sure how you would use this though.
I then set the set the DB to read only and single user mode. This would ensure I was the only user connected.
I then ran the script and set everthing back to normal.
Hope this helps.
The wonderfull thing about the USA is you guys are at work and I am on my way out for a couple drinks. Sorry, had to rub that in!
BTW: Tuesday was not to bad. Chin up! 🙂Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks Crispin, Have one for me!
-
I opt for Steve's suggestion of creating a new role, adding the users to the new role before load and removing after the load sounds good.
.
)
) and a kill will force a rollback. You can also, if needed, get status of the rollback. Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply