February 8, 2011 at 1:00 am
I CREATED A TABLE:
create table importdata.dbo.guesttable(guestname char(5))
I DENIED THE SELECT OPERATION ON THE ABOVE TABLE:
deny select on importdata.dbo.guesttable to guest
I EXECUTED THE SELECT QUERY, BUT IT SUCCEEDED, INSTEAD OF BEING DENIED????
select * FROM importdata.dbo.guesttable ---DENIED THEN ALSO IT SHOWS THE TABLE DATA
----------------------------------------------------------------------------------------------------
I CREATED THE SAME TABLE (THIS TIME IN THE GUEST SCHEMA):
create table importdata.guest.guesttable(guestname char(5))
ON DENYING THE SELECT OPERATION ON THE ABOVE TABLE IT GIVES ERROR
deny select on importdata.guest.guesttable to guest--gives error can't deny to yourself
WHY SUCH ERROR OCCURS?
PLEASE CAN ANYBODY EXPLAIN ME FULLY THE CONCEPT OF DBO, GUEST SCHEMAS IN SQL, AND PERMISSIONS
February 8, 2011 at 2:57 am
These are the very basic question and you should be explrong the answers by yourself instead of looking direct answer from someone.
----------
Ashish
February 8, 2011 at 6:08 am
thank you sir,
ok can you tell me this much:
I CREATED A TABLE:
create table importdata.dbo.guesttable(guestname char(5))
I DENIED THE SELECT OPERATION ON THE ABOVE TABLE:
deny select on importdata.dbo.guesttable to guest
I EXECUTED THE SELECT QUERY, BUT IT SUCCEEDED, INSTEAD OF BEING DENIED????
select * FROM importdata.dbo.guesttable ---DENIED THEN ALSO IT SHOWS THE TABLE DATA
----------------------------------------------------------------------------------------------------
on denying the select permission on the guesttable also when i run the select command , then there is no message or alert, it simply executes the query
instead of blocking it.
February 8, 2011 at 6:29 am
1) Open SSMS--Secuirty--Logins
2) Right click on your required logins and select properties
3) Go in user mapping tab and you will see that login have public access on database. Uncheck the required database and then you will not be able to run select query.
----------
Ashish
February 8, 2011 at 8:22 am
hi,
execute as user='guest'
select * from dbo.guesttable
This will show that the select permission is denied for the user guest.
February 9, 2011 at 12:22 am
thanks a lot sir for your reply,
but when i uncheck the database and click ok, then it shows an error as :
Rename failed for user 'dbo' (Microsoft.SqlServer.Express.Smo)
An exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.Express.ConnectionInfo)
Cannot alter the user 'dbo'.
February 9, 2011 at 12:30 am
THANK YOU VERY MUCH MR.AVINASHILY FOR YOUR REPLY
HELPED ME A LOT.
THANX A LOT
WOULD BE LOOKING FORWARD FOR MORE HELP FROM YOU.
THANX FOR THE GREAT FAVOR.
February 9, 2011 at 12:30 am
can you tell me what exactly you were doing and which account you connected with SSMS?
----------
Ashish
February 9, 2011 at 12:58 am
dear ashish sir,
i created a login :
create login bob with password='bobpass'
i did not create any user for the above login
i logged into sql server using above credentials
then i entered into database:
use importdata
then i created a table:
create drop table importdata.guest.guesttable(guestname char(5))
then i denied the permission --gives error can't deny to yourself
deny select on importdata.guest.guesttable to guest
when bob is the dbo, then why it is giving error for guest
February 9, 2011 at 1:04 am
then i created a table:
create drop table importdata.guest.guesttable(guestname char(5))
this query will give syntax error. How can u use create and drop together?
----------
Ashish
February 9, 2011 at 1:06 am
also, only a login 'bob' can be made a dbowner or a user 'bobuser' mapped to that login can also be made a dbowner by using:
can this be done---sp_changedbowner bobuser
February 9, 2011 at 1:06 am
sorry, there's no drop, by mistake i typed it
February 9, 2011 at 1:10 am
i entered sql server using admin credentials by default windows authentication.
now i create a login 'bob'.
what happens when i create a login in sql?
it becomes automatically the dbo i think?
means it can do anything with all databases?
is creating a user for login 'bob' necessary if i want to block table creation,selection etc?
February 9, 2011 at 1:20 am
You could refer below link which would give you an idea.
M&M
February 9, 2011 at 1:36 am
dear sir,
--FIRSTLY,i CREATE A LOGIN TO ENTER INTO SQL SERVER WITH SQL AUTHENTICATION
create login employeelogin with password='employee_login'
--CREATE A USER FOR THE ABOVE CREATED LOGIN
create user employeeloginuser for login employeelogin
--the above created user will by default have no permissions on the table
--but he would have to be granted permissions
--so i executed this query on emp table, and, it gave the 'select permission denied error' which is fine
EXECUTE AS USER='employeeloginuser'
select * from emp
--but now when i grant the permission on the table emp using below query:
grant select on emp to employeeloginuser
then it says that-----Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
please tell why so? and how do i grant the permission to employeeloginuser to select a table
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply