November 25, 2009 at 7:47 am
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?
November 25, 2009 at 8:37 am
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/
November 25, 2009 at 8:48 am
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
November 25, 2009 at 9:26 am
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.
November 25, 2009 at 9:31 am
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.
November 25, 2009 at 9:43 am
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?
November 25, 2009 at 9:51 am
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
November 25, 2009 at 10:07 am
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
November 25, 2009 at 10:46 am
That was it! Thanks for your help.
November 26, 2009 at 2:23 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply