How to restrict an user to do not take the Script of DB or table

Question

Post reply

How to restrict an user to do not take the Script of DB or table

Chowdary's

SSCrazy

Points: 2978
More actions
September 5, 2014 at 6:32 am

#293002

Dear All,
How to restrict an user to do not take the Script of DB or table.etc...
Actually i have created an user with only Read operation ,that is working fine but he can take the Script of table or DB by using the Srcipt Table As ....then he can make a new database with the existing Databases Data.
So now i want to restrict him to do not have the Right to take the script of the Tabe/Database/etc....
Pleas help me out.
Regards
Chowdary..
Regards
Chowdary...

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Chowdary

Have you tried denying the VIEW DEFINITION permission?

John

Chowdary's SSCrazy Points: 2978 More actions · Answer 2

Thank you Mr.John for your valuable reply.

Can you pls give me the Clear declaration on this as am new to this DBA field i cant able to do that.

Where can i do that settings and how to deny the View Definition for a particular Login.

Regards

Chowdary....

Regards
Chowdary...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Open up Books Online (the SQL help file) and search for DENY

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chowdary's SSCrazy Points: 2978 More actions · Answer 4

Hi Gail Shaw,

I did R&D and found the solution for that View Definition,

But here that Particular login cannot able to see the DB name,Tbl names,Sp names Etc....

Database name we can provide to that login but we cant provide the tbls,Sp name for each time whenever he wants.

Of course there is option like "select * from sysobjects where xtype ='u'/'p'...etc", By using this he can find the Tbl,Sp names etc..but each time he cannot go to that command.

Is there any solution to view only table,Sp names ....Etc.

Regards

Chowdary..

Regards
Chowdary...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

He's got to query the DB but isn't allowed to know the table names? How does that work? How does someone write queries without knowing table and column names?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chowdary's SSCrazy Points: 2978 More actions · Answer 6

He's got to query the DB but isn't allowed to know the table names? How does that work? How does someone write queries without knowing table and column names?

GilaMonster ,

Yes,We have given the DB name to that Login..

Here's is what i want,

Actually we are going to give the server access to the remote access for that we had created a new Login for that person.But we don't want to allow him to View Definition of the DB,Tables or sp....

For that i used the following

"use DBname

go

DENY VIEW DEFINITION TO LOGINNAME " and it is worked for me,The newly created login is not able to view the definition as well as table name and all the other objects,

We want to allow him to see the Objects but do not allow him to see the Definitions.

Can you pls help me out..

Regatds

Chowdary...

Regards
Chowdary...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Hang on, I'm confused.

You said you don't want him to see the definitions of the objects.

Then you said that you tried denying view definition and it worked, the login was not able to see the definitions.

If that's the case, then you've achieved your requirements, what more help is needed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chowdary's SSCrazy Points: 2978 More actions · Answer 8

Dear Gail Shaw Sorry for that,

Denying is worked,But here if i login with that newly created login i cant see the table names and all,Database name we know but we don't know the tables name and SPs name ,So they can't work on that DB without knowing the table name and all,

We want to display the tables, sps and all but we don't want to allow him to see the definition of those Objects due to some security reasons.

So how can i display the objects with some restrictions...

Regards

Chowdary...

Regards
Chowdary...

Chowdary's SSCrazy Points: 2978 More actions · Answer 9

In My DB there are 30+tables but here those tables are not showing same like SP also,

If i login with SA i can able to see those ,But if login with xyz which is restricted with Denyied View definition i cant see those tbls and SP's

PFA...

Regards
Chowdary...

Chowdary's SSCrazy Points: 2978 More actions · Answer 10

At last i got the solution for my Question.

By using the below Statements

deny select on sys.Columns to Login_Name,

deny select on sys.All_objects to Login_Name,

etc...

Once again thank you all for ur support....

Chowdary...:cool:

Regards
Chowdary...