November 6, 2003 at 12:36 pm
We have an NT login who is part of an operations group which group is also an SQL Server group with appropriate permissions for their work. How can I 'cut down' this one user's access while leaving him in the operations group?
November 6, 2003 at 12:42 pm
Try sp_denylogin to this NT Login in SQL Serevr.
November 7, 2003 at 11:46 am
If this login still needs access to the server to run a sibgle stored procedure, what can I can to implement that after sp_denylogin?
November 7, 2003 at 1:21 pm
You could deny permission object by object, but could be a lot of work.
November 7, 2003 at 2:23 pm
quote:
If this login still needs access to the server to run a sibgle stored procedure, what can I can to implement that after sp_denylogin?
Once he has been denied, he even can't login to the SQL Server. What you need to do is to grant him to login SQL Server and permission to run that particular stored procedure in user database only.
Be aware there is a 'guest' user in master database who is member of 'public' role. Because everyone is the member of 'public' role, so he can select data from many tables/views and run some stored procedures in master database the 'public' role has been granted without be granted to access master database. In order to prevent him to do that, Grant him to access master database and denied him to access any objects, it will be quite lots work to do as laue mentioned.
Edited by - allen_cui on 11/07/2003 2:26:12 PM
November 13, 2003 at 4:12 pm
Keep login, but add user to db_denydatawriter if you don't want them to be able to modify any data and db_denydatareader if you don't want them to even be able to see any data.
SP will still work, even if it reads/writes.
Example:
create procedure up_junk as
select * from (your table)
grant exec on up_junk to OperationsRole --BadUser is already a member
go
EXEC sp_addrolemember 'db_denydatareader', 'BadUser'
Now log in as BadUser and try the following:
[/code]
select * from (your table) --denied
go
exec up_junk --OK
[/code]
Bill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply