April 26, 2007 at 6:29 am
We're trying to prevent a new employee access to our production database.
I would of thought that all you needed to do is create a login for that user and specifically deny connect permission and/or disable the login.
Try as I might, I can't manage to disable/block the user.
What am I missing?
thanks,
Chris
April 26, 2007 at 7:16 am
Hi,
Assuming your using NT / AD authentication, I would look at the users group memberships and see what permissions those groups have.
But if you create the user, with no permissions to any database - but a deny on the login, it should lock them out.
Is the application / ODBC using SQL authentication ?
Just a few thoughts.
Regards,
Ian.
April 26, 2007 at 7:19 am
Check that the user is not an administrator on the production server. If so, he can get access to your SQL Server as sysadmin through the BUILTIN\Adminstrators account.
April 27, 2007 at 6:44 am
We're using windows authentication. What we are trying to do is to deny the developer DIRECT access thru the SSMS interface. We dont want him to be able to change procs/tables, etc. However, the user does have 'public' access to the database, since the user needs to access an application that uses the data.
April 27, 2007 at 7:01 am
Hi,
I would suggest you might wish to consider a database trigger as an alternative method for restricting who changes procedures / tables and you can use them to roll back transactions when certain commands are issued.
Below is an example of what I used to lock some developers out of our production system. ( you just want to change the drop_table to what every commands you want to stop)
Ian.
CREATE
TRIGGER [Safety]
ON
DATABASE
FOR
DROP_TABLE , ALTER_TABLE , CREATE_TABLE
AS
PRINT 'Access Denied'
ROLLBACK;
GO
ENABLE
TRIGGER [Safety] ON DATABASE
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply