Differences: Grant, Deny, Revoke

I would like to know the differences: Grant, Deny, Revoke

Tks in advance

View Replies



Hi Guys,
We are using MS SQL 2005. I am ask to remove the PUBLIC rights to the objects listed in the following query in the master DB:

SELECT sysusers.name, sysobjects.name,sysprotects.action FROM sysobjects, sysusers, sysprotects WHERE sysobjects.id = sysprotects.id AND sysprotects.uid = sysusers.uid AND sysprotects.protecttype = 205

I keep having the "Cannot find the object [Objectname], because it does not exists or you do not have permission."

How do I create a query to remove the PUBLIC rights at a single run. (There are total of 1660 items, please dun ask me to write the DENY or REVOKE statement 1660 time )

How do I DENY the rights for objects starting with the prefix "dm_" or items like "TABLE PRIVILEGES"
Thanks guys Any help on this is greatly appreciated.

View Replies View Related


Can someone explain ..what is the basic difference between TSQL Stmts Grant and Revoke?
A reply will be highly appreciated

View Replies View Related

Grant/Revoke Column Privileges

How can I set / view column privileges.

I want to remove the select privilege from a salary column to a certain group of users.


View Replies View Related

GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] Prevent Grant From Being Automaticly Add To Each Column

GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?

Is there a way when you issue a grant select to a table or a view to not also grant select for each column.

The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.

The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.

View Replies View Related

How To Grant Create View Access Without Grant Alter On Schema::

In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.

Is there any way to grant this privilage without granting alter on schema also?

View Replies View Related


Hey, how come

REVOKE ALL FROM mepuser, mepnotes

Doesn't work?

I still see execute permissions

View Replies View Related

Revoke Permissions TO Stored Procedure

I have written an stored proc that reads from a text file and executesthe script as dynamic sql.If the text file contains malicious code,I want to be able to detect itand prevent the stored procedure from executing.I've tried revoking delete,insert,update rights all tables in thedatabase to the user .I then granted execute rights to the stored procedure for the sameuser. But the user is still able to delete a record from the table byexecuting the stored procedure.Is there any means to I revoke,insert,delete ,update rights to a storedproc?

View Replies View Related

Revoke Connect Guest User

I've used the following:
EXEC sp_MSforeachdb 'USE [?];

And this is what I get:
Msg 15182, Level 16, State 1, Line 2
Cannot disable access to the guest user in master or tempdb.
Msg 15182, Level 16, State 1, Line 2
Cannot disable access to the guest user in master or tempdb.
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'GUEST', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'GUEST', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'GUEST', because it does not exist or you do not have permission.

When I do this:
EXEC sp_MSforeachdb 'USE [?];
SELECT ''[?]'' AS DBName,* FROM sysusers;'

The guest sid for all tables shows 0x00, is this the reason I get above errors?

View Replies View Related

Revoke Execute Permission From A Login

Dear All,

I need to revoke execute permission from sp_configure (SP) from a user named(a) which do not exists in master database.


Mohd sufian

View Replies View Related

Schema Revoke SELECT Permission

HI, I have set up a database with 5 users, USER1 has default schema USER1, USER2 has default schema USER2 and so on. My problem is that I want to revoke select permission on schema USER1 to user USER2. I issued the following TSQL in SSMS:


Even though I did that, I can still log on as USER2 and be able to issue SELECT statements on USER1 schema tables. The only way I can do to avoid the SELECT on USER1 schema is to DENY select on USER1 schema. Is it normal?

Thank you for your help,


View Replies View Related

Trouble With Cursor Designed To Revoke Object Access.

Hi everyone. I am having difficulties with a cursor that I am trying to write. The purpose of the cursor is to loop through all tables in a selected Database and revoke "Select" access to that table for the indicated role (RoleToRevoke). I am getting the error on the @name variable within the REVOKE statement. I have placed a comment indicating where I am getting the error. Is there a way to have sql server interpret this @name variable within the REVOKE statement? Thanks for your help.

Code Snippet

USE [Database_Name];

Declare cursorExample Cursor for
from information_schema.tables
Where TABLE_TYPE='Base Table'
and TABLE_SCHEMA='dbo'

Declare @name as varchar(255)
Declare @ErrorSave as int
Declare @ErrorCount as int

Open cursorExample

Fetch Next from cursorExample into @name

SET @ErrorSave = 0
SET @ErrorCount = 0

Begin Tran

While @@Fetch_Status=0


--Getting Error on Line below on @name

IF (@@ERROR <> 0)


Print 'Error Revoking Access to Table: ' + CAST(@name AS varchar(75))
SELECT @ErrorCount = @ErrorCount + 1
IF (@@ERROR = 0)


Print 'Successful Revoking Select Rights on Table: ' + CAST(@name AS varchar(75))

SELECT @ErrorSave = @ErrorSave + 1
Fetch Next from cursorExample into @name


IF @ErrorCount = 0


PRINT 'Total Tables Affected:' + CAST(@ErrorSave AS varchar(75))



Close cursorExample
Deallocate cursorExample

View Replies View Related

6.5 Question - Unable To Revoke SA Permission From Local Admin Group

We have a SQL Server 6.5 instance that has yet to be upgraded to SQL 2000. (Long story). Anyhow, I'm trying to revoke SA permission from the local administrators group, which SQL Server grants by default during setup. I followed the instructions on BOL, but no luck. Here is what I've tried.

Changed my desktop client from named pipes to TCP/IP
Verified the server client allows TCP/IP and named pipes (needed for old applications)
Changed security mode to Mixed
Created a local group called DBA_Admin
Granted DBA_Admin SA permission through SQL Security Manager
Revoked SA permission from Administrators group (local admin group on server)
Added my domain group to DBA_Admin

Unfortunately someone who is a member of the local administrators group, but not DBA_Admin group, is able to still access the SQL Server instance from Enterprise Manager using a trusted connection. Why???????????

Thanks, Dave

View Replies View Related

Revoke Public Acccess To Msdb System Stored Proc

Can we remove execute access to the public role to all the system stored procedures ? Has anyone done this & are there any issues with doing this for lockdown. Let meknow


View Replies View Related

Deny Access

Hello, I Have created a folder in the IIS called Webreporting. I used sql server to generate HTML into that folder. I make NT security to that folder so certain people can view the report. Unfortunatelly my code to generate the scheduled HTML report failed and gave this error message:

Server: Msg 16821, Level 11, State 1, Procedure sp_makewebtask, Line 125
SQL Web Assistant: Could not open the output file.

View Replies View Related

Deny Permissions

I have a customers table and a stored procedure for deleting records from this table. I connect to sql using a connection string with sql server authentication using a user I've set up in sql server. This user has roles public, db_datareader and db_datawriter and has permissions to execute all my custom stored procedures. Up to this point everything is fine and I can insert, update and delete. Happy days.

If I then select the permissions for the user and deny delete permission on the customers table I thought that because deny overrides everything else I could not delete a customer from my application. Much to my surprise, I can. Why is this?

View Replies View Related


Is is possible to Grant a 'DENY DELETE' on a table to the 'sa'user or will the fact that he is administrator over ride the restriction.


Fred Scuttle

View Replies View Related

How To Deny DMV To Public?

Hi Guys,

I am unable to deny DMV rights to public. I have already ran the SQL query successfully:

However when I check my master DB, the public still have rights to all the dm_***** objects. Am I doing it wrong or is there any steps I missed out? Can anyone help please?
Thanks a million.

View Replies View Related


Hello All,

I want to deny all user connections to be denied to a Database cluster. How best to do it?

As per my research I can use DENY CONNECT SQL TO instead of sp_denylogin but how can I deny login to say NORTHWIND database?

Also To kill all the connections to the database will the following command work best or should I use something else?



View Replies View Related

Deny An Application?

Hello everyone,

Is there a way to deny a specific application from connecting to SQL Server? There is an application some of my developers use that connects with Windows Authentication that I would like to block from my production server. I can't deny the user's logins because they need to be able to connect to the server from other applications.

Does anyone have any suggestions?

Thanks in advance,


View Replies View Related

EXECUTE Permission Deny

Any one can help me, below error messages for reference, thanks!
Exception Details: System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.Source Error:

Line 96: cmdMid.Connection = conMid;
Line 97: cmdMid.CommandText = "exec sp_insertspend '" + uid + "','" + Mid + "','" + status + "','" + spend + "'";
Line 98: cmdMid.ExecuteNonQuery();
Line 99: conMid.Close();
Line 100:Source File: f:Microsoft Visual Studio 8WebSoccermain.aspx.cs    Line: 98 Stack Trace:

[SqlException (0x80131904): EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734934
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
_Default.btnbet_Click(Object sender, EventArgs e) in f:Microsoft Visual Studio 8WebSoccermain.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

View Replies View Related

Deny Remote Logins?

I was wondering if there is anyway to deny remote logins on SQL Server 7 (other then pulling the network cable out of the back :) ) so that nothing can login during a service pack upgrade?

Many thanks in advance

View Replies View Related

Deny SQL User Access

I know this seems like a strange ask, but we have a common user and pass in most of our web applications and this user requires DBO access to the database, no problem is occasioannly we need to let a developer look at an issue on a production server, so we port them through to sql through the firewall so they can see it it, and they normally login wih there domain/username and this way they are restricted to what they can see and do. However all developers know the SQL user and pass used in the web app, they also know its a DBO, so this means they can login with these user details and have access to everything.

My question is how can i lock this down so i can deny access from the SQL management console for this particular user, but still allow the user to act as a DBO for the web applications.

your help here is appreciated.



View Replies View Related

Deny Access For Sysadmin


I need to give a sysadmin role to a user and I need to prevent that user to access some tables in specific databases...

thanks in advance...

View Replies View Related

Deny Insert Of Columns

Hi all,

I'm trying to write a database table that is ONLY accessiable to ONE login / DB user and restrict access to what that user can do with the table.

At the moment I have granted SELECT & INSERT permissions but some of the fields in the table should also be restricted, but for some reason SQL doesn't allow a deny on INSERT for columns.

It seems strange that you can deny an UPDATE permission on a column but not INSERT, so users can create new lines of junk into fields you dont want them to, but cant change that junk afterward??

Anyone have any ideas how I can prevent this user from altering these fields at all?

Thanks in advance

View Replies View Related

Deny Access To A View

I'm having trouble creating a read-only view. I've got 1 or more tables that I wish to remain updatable but I want to create a view that covers the table and/or spans all the tables. However, I want the view to be select only. I can't seem to get it to work.

DENY UPDATE ON [dbo].[MyView] TO [dbo] CASCADE

All that seems to execute my dbo user can still use...

Update MyView set SomeID = SomeID + 10

Plus, ideally I just want to say, DENY UPDATE ON VIEW TO ALL

Any thoughts?

View Replies View Related

Deny Access To Master

I have a login associated with a single user on a database (not master). I want that user to be able to only see what I've specified in the securables.

Now I've created a ODBC connection using that login. The problem is that the user can also see the master db info. I was expecting to see just the one view I created and granted the user to view. How do I get rid of all the master db stuff?

View Replies View Related

Deny Permissions Not Being Honored!!


Im trying to lock down PCI sensitive data columns in some product databases
from our developers. We need developers to have data reader rights to the
production database to perform general maintenance and troubleshooting of the
application BUT minus the specific secured columns like credit card number,

I have a user role setup called RWE created by:

A standard SQL user is placed inside this role allowing them full read,
write, and execute rights on everything in the DB which is fine.
BUT, now we want those same rights except for the sensitive data files so I
updated the rule with the following script:

DENY SELECT ON [dbo].[TableNameHere] ([strCC]) TO [RWE]

Logging in a developer and doing a SELECT * FROM TableNameHere throws a
permission error on the strCC column as expected, so far so good.

But, I did a SELECT * FROM ViewThatContainsField_strCC and shows them the
denied field. Oh, oh! I also did EXEC spStoredProcThatShows_strCC and again it
shows the denied credit card field. Again, oh, oh!

So, long story short, Im stuck. My understanding of SQL permissions, I was
under the understanding that placing a deny at the lowest level (column)
should throw errors in all methods of access that column (either by direct
select, or select through views, procs, etc).

Any suggestions or any hints on what Im missing here?

Thx ahead of time!

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved