How can user script out table in SSMS without permissions to change the schema.

Question

Post reply

How can user script out table in SSMS without permissions to change the schema.

Rodney Workman

SSChasing Mays

Points: 651
More actions
November 25, 2009 at 7:47 am

#213035

My user is a developer with db_datareader and db_datawriter roles in a database. In SQL 2000 the user could script out table objects in query analyzer even though they did not have permissions to change the schema. In SQL 2008 with only datareader and datawriter, SSMS give the user an error is they attempt use the "script table as" feature. Its easy to reproduce, just create a new SQL Server login and grant datareader and datawriter to a given database. Have that user try to "script table as" and you have the error.

The error message is a dialog box titled "Microsoft SQL Server Management Studio". The error message is "Script failed for table 'dbo.tablename'. " There is additional information which reports "insufficient access rights".

The user does not want to alter the schema, they just want to see how the object was created.

Does anyone have some background with this behavior?

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Rodney Workman (11/25/2009)

My user is a developer with db_datareader and db_datawriter roles in a database. In SQL 2000 the user could script out table objects in query analyzer even though they did not have permissions to change the schema. In SQL 2008 with only datareader and datawriter, SSMS give the user an error is they attempt use the "script table as" feature. Its easy to reproduce, just create a new SQL Server login and grant datareader and datawriter to a given database. Have that user try to "script table as" and you have the error.

The error message is a dialog box titled "Microsoft SQL Server Management Studio". The error message is "Script failed for table 'dbo.tablename'. " There is additional information which reports "insufficient access rights".

The user does not want to alter the schema, they just want to see how the object was created.

Does anyone have some background with this behavior?

Hi Rodney,

Try this

Alter login test123 with password = 'pass' unlock, check_policy = off, check_expiration = off

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Lowell SSC Guru Points: 323482 More actions · Answer 2

wierd Rodney; I tried to duplicate your steps,but I AM able to use the script as function in SSMS when i login as my test user. i wonder why it works for me but not you.

here's the scripted steps i did, assuming you have a database named "SandBox"



IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'SandUser')

BEGIN

EXEC MASTER.dbo.sp_addlogin @loginame = N'SandUser', @passwd = 'NotARealPassword', @defdb = N'master', @deflanguage = N'us_english'

USE [SandBox]

GO

CREATE USER [Sanduser] FOR LOGIN [SandUser] WITH DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember N'db_datawriter', N'Sanduser'

GO

USE [SandBox]

GO

EXEC sp_addrolemember N'db_datareader', N'Sanduser'

GO

--Here I attempted to loginto SSMS Object Explorer as the user "SandUser",

--browsed to SandBox, and tried to script any of the tables without an error

--here i just diagnosed my rights to be sure:

select db_name()

select

  user_name()  AS [user_name],

  suser_name() AS [suser_name],

  current_user AS [current_user],

  system_user  AS [system_user],

  session_user AS [session_user],

  user         AS 

select * from fn_my_permissions(null,'SERVER')

select * from fn_my_permissions(null,'DATABASE')

SELECT USER_ID() AS [USER_ID]

, USER_NAME() AS [USER_NAME]

, SUSER_ID() AS [SUSER_ID]

, SUSER_SNAME() AS [SUSER_SNAME]

, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin]

, IS_MEMBER('db_owner') AS [Is_DB_owner]

, IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin]

, IS_MEMBER('db_datareader') AS [Is_DB_Datareader]

, IS_MEMBER('db_datawriter') AS [Is_DB_Datawriter]

, ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

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!

Rodney Workman SSChasing Mays Points: 651 More actions · Answer 3

Lowell, that absolutely did work. I do not know how the original developer login was added but I added by test login using the SSMS GUI. I used your sample script and it worked as expected.

Now I am curious to see what the delta is between a user created with the GUI and a user created with the script.

Thanks for you help.

Rodney Workman SSChasing Mays Points: 651 More actions · Answer 4

Rodney Workman

SSChasing Mays

Points: 651

November 25, 2009 at 9:31 am

#1084160

Scratch that. My "SandUser" can only script out a few tables. Most tables still gives me an error. So my "SandUser" and my original test user have the same capabilities.

Rodney Workman SSChasing Mays Points: 651 More actions · Answer 5

I believe the problem for these users are the defaults that exists for the table columns. When a table has a default the user can not script that object out. When a table does not have a default the user can script the object out.

Anyone know why a table default would prevent a user from using ssms to script out a table when they don't have permission to alter a table?

Rodney Workman SSChasing Mays Points: 651 More actions · Answer 6

Rodney Workman

SSChasing Mays

Points: 651

November 25, 2009 at 9:51 am

#1084182

The user has the same problem using the "script function as".

Based on the following link, I think maybe this is a bug?

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125055

Lowell SSC Guru Points: 323482 More actions · Answer 7

Rodney i was able to recreate the error for tables that contain a default value, you are right.

a bandaid i found that works was to go to master and run this command



Grant View Any Definition To SandUser

once my SandUser had that, he was able to script out any of the tables in any database he had access too: master or SandBox.

--edit-- it seems i could do the same at the database level witht his command:

Grant View Definition To SandUser

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!