Schema

Question

Schema

mike 57299

SSCrazy

Points: 2529
More actions
February 14, 2014 at 1:51 pm

#304425

Can someone please give me example SQL code to do the following:
* Create Scheme xyz
* Add views to schema xyz
* create user that only can see view in xyz
* make sure user can't see any tables in the database
-- Note: All tables created as dbo
Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

Have you even tried looking for "CREATE SCHEMA" on google?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mike 57299 SSCrazy Points: 2529 More actions · Answer 2

Luis Cazares (2/14/2014)
Have you even tried looking for "CREATE SCHEMA" on google?

Yes. I tried it - made the user - made the schema - logged in as the user - couldn't select the "customer" table but could find out it was there - couldn't see the view.

So yes... I tried and I then came here seeking help.

Mike

P.S. Why waste the keystokes asking me if I looked - if you don't want to help - no problem - but do you have to give grief? Maybe, I am super swamped and just need some help.

Lowell SSC Guru Points: 323482 More actions · Answer 3

take a look at this post i made a few years ago, it's got a nice example of a couple of schemas you can fiddle with.

http://qa.sqlservercentral.com/Forums/Topic1216364-1550-1.aspx#bm1216431

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

I'm sorry, your answer makes it completely different. I'm sorry but many people come here and ask questions without even trying anything (some people believe that is easier to write a post here than to press F5 on SSMS).

To grant or remove permissions, you need to be sure you're not the user receivng the permissions.

You have a group of statements to manage permissions: GRANT, DENY, REVOKE.

You can GRANT permissions on the views (or the new schema) to allow the user to work with it. And you can DENY permissions on the dbo schema.

Can you post exactly what you've tried? Have you done it with T-SQL or with the graphic interface?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mike 57299 SSCrazy Points: 2529 More actions · Answer 5

mike 57299

SSCrazy

Points: 2529

February 14, 2014 at 2:24 pm

#1689234

Wow. Thank you! This will really help.

Mike

mike 57299 SSCrazy Points: 2529 More actions · Answer 6

Luis Cazares (2/14/2014)
I'm sorry, your answer makes it completely different. I'm sorry but many people come here and ask questions without even trying anything (some people believe that is easier to write a post here than to press F5 on SSMS).
To grant or remove permissions, you need to be sure you're not the user receivng the permissions.
You have a group of statements to manage permissions: GRANT, DENY, REVOKE.
You can GRANT permissions on the views (or the new schema) to allow the user to work with it. And you can DENY permissions on the dbo schema.
Can you post exactly what you've tried? Have you done it with T-SQL or with the graphic interface?

I understand that some post without trying. That is life. Does it really matter if they tried? If they understand the solution you present or they learn something, isn't it what the forum is really about? Yes, some may take advantage of it. But some, like me, may read what others have asked and try to figure it out.

Unfortunately, I the testing I did, I did it completely in the interface.

Mike

Lowell SSC Guru Points: 323482 More actions · Answer 7

mike 57299 (2/14/2014)
Can someone please give me example SQL code to do the following:
* Create Scheme xyz
* Add views to schema xyz

the above are not necessary to make other objects not-visible/accessible.

you can do all that with a simple user, without the deep complexity of alternate schemas

* create user that only can see view in xyz
* make sure user can't see any tables in the database
-- Note: All tables created as dbo
Thank you!

-- a table to prove our user cannot see it

create table dbo.noTableForYou(id int,otherData varchar(30) )

--some sample data

insert into dbo.noTableForYou

select top 5 object_id,name from sys.objects

GO

--a view of the data

create view dbo.xyz

AS

SELECT * FROM dbo.noTableForYou

GO

--our test user

CREATE USER TestUser WITHOUT LOGIN

--grant the only object he can see

GRANT SELECT ON dbo.xyz TO TestUser

--no test as our user

EXECUTE AS user='TestUser'

--can i see the data?

SELECT * FROM dbo.xyz

--can i see any objects ?

select * from sys.objects

--can i directly access the underlying table, if i know it exists?

SELECT * FROM dbo.noTableForYou

--change back into sysadmin

REVERT;

--cleanup my mess

DROP USER TestUser

DROP VIEW dbo.xyz

DROP TABLE dbo.noTableForYou

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183686 More actions · Answer 8

mike 57299 (2/14/2014)
Does it really matter if they tried? If they understand the solution you present or they learn something, isn't it what the forum is really about?

It matters to me. I'm willing to give many solutions, but if people never try, they will never learn. I want people to learn how to learn and find solutions on their own.

Unfortunately, I the testing I did, I did it completely in the interface.
Mike

As a suggestion, now that you're learning. If you find it easier to work with the GUI, use the option to generate scripts instead of clicking OK. It will help you understand what you're doing and you can learn more.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mike 57299 SSCrazy Points: 2529 More actions · Answer 9

Followup:

Here is a picture of what I see if I access the SQL data via Access/ODBC. How do I get rid of the Information_Schema & Sys lines?

(Picture attached.)

Mike