x

issue with user login permission on database

i have given a user with datareader/datawriter/execute permission on a database. still then user is able to see procedure as locked icon and more over can't run sp_helptext.. what is issue here what more permission or things i can do for that user.. looks issue is with something else.. help..!
more ▼

asked Nov 03 '11 at 10:13 PM in Default

Sri 1 gravatar image

Sri 1
11 9 9 10

(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest
For a user to see the definition of user objects, it must have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. I guess in your case you should give VIEW DEFINITION permission. Cheers.
more ▼

answered Nov 03 '11 at 11:34 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

i think that will not work as objects are encryted..
Nov 04 '11 at 12:32 AM Sri 1

 SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],  sp.name AS [Name],  sp.object_id AS [ID],  sp.create_date AS [CreateDate],  sp.modify_date AS [DateLastModified],  CAST(CASE WHEN smsp.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp  ON smsp.object_id = sp.object_id WHERE smsp.definition IS NULL -- This identifies an encrypted object  AND sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')  AND sp.is_ms_shipped = 0 SELECT sp.type, sp.type_desc  , COUNT(smsp.definition) AS UnencryptedObjects -- only non-null or unencrypted objects will be counted  , COUNT(*)-COUNT(smsp.definition) AS EncryptedObjects  , COUNT(*) AS Total FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp  ON smsp.object_id = sp.object_id WHERE sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')  AND sp.is_ms_shipped = 0 GROUP BY sp.type, sp.type_desc 
--- so milliion dollar question in my mind is, if user A can see the script as i myself tried his login and not seeing any lock symbol and can easily pull script/ why there is a issue with USER B as his login showing lock on object as well getting error on pulling scrpt.
Nov 04 '11 at 01:04 AM Sri 1
as per Pavel if it is encryted, no one can see the script..
Nov 04 '11 at 01:05 AM Sri 1
Can you please let us know that there is no 3rd party tool in installed? and there is no more details you can add?
Nov 04 '11 at 01:11 AM Usman Butt
one user is master account from which i am using and another is a simple user. but question is now that if above script showing all are encryrted, why one user can see script and other can't and see lock symbol.
Nov 04 '11 at 01:15 AM Sri 1
(comments are locked)
10|1200 characters needed characters left

to use a sp_helptext on an object you need to hvae ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION permission on that object.

So if you have only datareader/datawriter/execute permission, than you will not be able to view the definition of the object.

The lock icon could also signal, that the object is encrypted. For such encrypted objects you will not eb able to view the definition even you have the above mentioned permissions.
more ▼

answered Nov 03 '11 at 11:44 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

but i have different user which is able to see the defination... y so...
Nov 04 '11 at 12:19 AM Sri 1
So as me and @Usman Butt stated, the user needs the ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION permission on that object. In case the other user can see the definition, then he has one of the mentioned permissions assigned directly or have effective permissions inherited.
Nov 04 '11 at 12:26 AM Pavel Pawlowski

yes you are right objects are encrypted... so is there any way and what other possiblility now..

i just confused now..pls suggest..
Nov 04 '11 at 12:32 AM Sri 1
This is very strange. Are you sure you or any other user can see the definition of encrypted stored procedure?
Nov 04 '11 at 12:36 AM Usman Butt

@usman.. sry my fault... from user login which can see having ddladmin and db_owner.. that is why he can see......

@Pavel and @Usman, so is there any other thing that i can do..to let other user can see defination or i have to give db_owner and ddlAdmin permission to see encrypted script... which can be very very dangerous...
Nov 04 '11 at 12:44 AM Sri 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x83
x22

asked: Nov 03 '11 at 10:13 PM

Seen: 1437 times

Last Updated: Feb 24 '12 at 11:51 AM