October 25, 2011 at 3:07 pm
This one has me stumped and google isn't helping...
When I use an ODBC system dsn and try to use MS query in excel or using an rdo datasource in crystal I don't see the fields that have user defined datatypes if the sql login I'm using is set up with db_datareader only rights.
Here's a simple test script to show it:
USE [master]
GO
/* create test database */
CREATE DATABASE [test]
GO
USE [master]
GO
/* Create SQL login */
CREATE LOGIN [ro_test_user] WITH PASSWORD=N'Test1Test', DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [test]
GO
/* Create SQL User */
CREATE USER [ro_test_user] FOR LOGIN [ro_test_user]
GO
USE [test]
/* Assign Data Reader rights to test user */
GO
EXEC sp_addrolemember N'db_datareader', N'ro_test_user'
GO
USE [test]
GO
/* Create User Defined datatype */
CREATE TYPE [dbo].[FACILITY_NAME] FROM [varchar](55) NULL
GO
/* Create Table That uses the new User Defined datatype */
CREATE TABLE T1 (
[int] NOT NULL,
[ftype] [int] NOT NULL,
[name] [dbo].[FACILITY_NAME] NULL,
[initials] [varchar](8) NULL,
)
GO
INSERT INTO T1 (code,ftype,name,initials)
SELECT 1,1,'Test Facility 1','TF1'
UNION
SELECT 2,0,'Test Facility 2','TF2'
UNION
SELECT 3,15,'Test Facility 3','TF3'
UNION
SELECT 4,22,'Test Facility 4','TF4'
UNION
SELECT 5,69,'Test Facility 5','TF5'
UNION
SELECT 6,9991,'Test Facility 6','TF6'
UNION
SELECT 7,21,'Test Facility 7','TF7'
The next step is to create a System dsn using the sql user ro_test_user. Then If I use this datasource in MS Query in excel the table t1 only shows up with the fields that aren't user defined types.
If I change the account to be ddl admin or db owner then the user defined typed field shows up.
Anyone else run into this and have a solution?
Thanks in advance.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
October 25, 2011 at 3:23 pm
Try this: GRANT VIEW DEFINITION TO LoginName
Jared
Jared
CE - Microsoft
October 25, 2011 at 3:31 pm
That worked Thanks!
The weird thing is that via an ADO connection in crystal it works without granting view definition, That part still confuses me.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
October 25, 2011 at 3:34 pm
Trey Staker (10/25/2011)
That worked Thanks!The weird thing is that via an ADO connection in crystal it works without granting view definition, That part still confuses me.
I just took a guess... I must be applying all I've learned from this site!
Jared
Jared
CE - Microsoft
October 25, 2011 at 4:00 pm
Thanks again for your help!
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply