I want to check the permissions given to a user not login on particular schema

Question

I want to check the permissions given to a user not login on particular schema

forsqlserver

SSCoach

Points: 18902
More actions
April 20, 2011 at 5:18 am

#235025

Please resolve my one confusion:
I have a test database in which there is a schema [sqluser_schema] and a user [sqluser1] which is mapped to a login [sqluser] SQL Authentication.
Now I have gone into the properties of schema-->Permissions and search the name of [sqluser1].I have found it.I have denied the permission of select and insert to this [sqluser1] user.
I have created 2 tables in this schema.
Now I want to check these permissions which I have applied.
How to do this?
Thanks

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Login as that user and try to do some inserts and selects on objects that belong to that schema. You can do that directly in SSMS easily enough.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Here's how I usually do it:

SELECT SYSTEM_USER -- should show me

EXECUTE AS LOGIN='login_to_test' -- allows me to impersonate the test login's security context

SELECT SYSTEM_USER -- should show login_to_test

-- test the new permission-set here...

REVERT -- drops me back into my security context

SELECT SYSTEM_USER -- should show me again

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 3

~~Login as that user and try to do some inserts and selects on objects that belong to that schema. You can do that directly in SSMS easily enough.~~

Thanks...But How can I login using a user,because does not have password...

and OPC.Three when applying your setting it is giving error below while I have login as sa:

Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the principal "sqluser1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

forsqlserver (4/20/2011)
OPC.Three when applying your setting it is giving error below while I have login as sa:
Msg 15406, Level 16, State 1, Line 1
Cannot execute as the server principal because the principal "sqluser1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

In your original post you said the login was named sqluser, not sqluser1.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Thanx..

By mistake I have run the sqluser1.

Problem is still same I want to test the user sqluser1's permission:-)

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

forsqlserver (4/21/2011)
Thanx..
By mistake I have run the sqluser1.
Problem is still same I want to test the user sqluser1's permission:-)

sqluser1 is a database user associated to the login...so if you do EXECUTE AS with the login associated with sqluser1, i.e. sqluser, then you'll be testing using sqluser1's permissions. Like this:

SELECT SYSTEM_USER -- should show me

EXECUTE AS LOGIN='sqluser' -- allows me to impersonate the test login's security context

SELECT SYSTEM_USER -- should show login_to_test

-- test the new permission-set here...

REVERT -- drops me back into my security context

SELECT SYSTEM_USER -- should show me again

You can also specify the user, but that has other implications towards cross-database permissions:

SELECT SYSTEM_USER -- should show me

EXECUTE AS USER='sqluser1' -- allows me to impersonate the test login's security context

SELECT SYSTEM_USER -- should show login_to_test

-- test the new permission-set here...

REVERT -- drops me back into my security context

SELECT SYSTEM_USER -- should show me again

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Thanks..

EXECUTE AS USER='sqluser1'

is giving error:

Msg 15517, Level 16, State 1, Line 1

Cannot execute as the database principal because the principal "sqluser1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

I promise I am not making this stuff up 🙂

Things to check:

1) are you in the right database?

2) does the username sqluser1 exist in that database?

3) do you have sa permissions on the instance, or have impersonate rights for the user?

The error message says it all...keep at it.

See if Example A in the BOL article for the EXECUTE AS command helps as well. It's an example doing something similar to what you're trying to do: http://msdn.microsoft.com/en-us/library/ms181362.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 9

I promise I am not making this stuff up
Things to check:
1) are you in the right database?
2) does the username sqluser1 exist in that database?
3) do you have sa permissions on the instance, or have impersonate rights for the user?
The error message says it all...keep at it.
See if Example A in the BOL article for the EXECUTE AS command helps as well. It's an example doing something similar to what you're trying to do: http://msdn.microsoft.com/en-us/library/ms181362.aspx

I am really glad for your reply.

I was trying it in the master database that is ok now.Execute as user is fine.

But When I am denying the select permission on sqluser1 from schema sqluser_schema then neither it is saving nor it is appying means select is running fine.

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Progress...excellent. Please post the code you're trying, including the DENY statement and the EXECUTE AS code you're using to test the permission change.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 11

use [test]

GO

DENY SELECT ON SCHEMA::[sqluser_schema] TO [sqluser1]

GO

Error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

--------------------------------------------------------------------

using

Execute As User='sqluser1'

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Who owns sqluser_schema?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

forsqlserver SSCoach Points: 18902 More actions · Answer 13

Thanks...

Who owns sqluser_schema?

The User sqluser1

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

Re-read the error message.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato