How do I grant permission? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I grant permission?

Author

Topic

enak
Starting Member

34 Posts

Posted - 2006-08-02 : 11:09:05

I have an ASP.NET application that I create reports using Crystal Reports. When I try to create the dataset I get this error:

CREATE TABLE permission denied in database 'MyDatabase'.

This is the SQL that I use:

SELECT Applicant.client_num, Interview.*, Applicant.sub_name,
Applicant.client_name, Applicant.lastname, Applicant.firstname
into [PrintInterview]
FROM Applicant, Interview
where Applicant.file_num = Interview.file_num and Applicant.file_num = 2720706

I am not a dba so I need your help.

Thanks,
enak

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 11:13:04

Two ways:

Firstly you could make the user a member of the DATA_READER group (and DATA_WRITER if you like). This is a very blunt instrument and provides READ (and WRITE) access to ALL user tables in a database.

Alternatively you can be more specific:

GRANT SELECT ON [Applicant] TO MyUserName

or you can create a Group, put MyUserName in that Group, and then do

GRANT SELECT ON [Applicant] TO MyGroupName

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-02 : 11:15:31

In ur application (either ASP.Net or Crystal Report), r u passing a query ?
is it a table creation query ? or what is it ?

If there is a relevent code please post that as well.

Srinika

enak
Starting Member

34 Posts

Posted - 2006-08-02 : 11:30:07

thanks for the replys. I have checked the permissions of the group that I use to grant access to the objects in the database. Then all have select but I don't know how to grant Create Table permissions.

This bit of code was written by someone else and they are out of the country for the rest of the week.
The table already exists in the database so I don't know why he is trying to creat the table.

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 11:32:25

Well spotted!

Its a

SELECT ... INTO ... FROM

query. Yes, you will need more "generous" permissions to do that, like DB OWNER for example.

If the [PrintInterview] table already exists you can do:

INSERT INTO [PrintInterview]
SELECT Applicant.client_num, Interview.*, Applicant.sub_name,
Applicant.client_name, Applicant.lastname, Applicant.firstname
FROM Applicant, Interview
where Applicant.file_num = Interview.file_num and Applicant.file_num = 2720706

(do a

DELETE [PrintInterview]

first if you want to pre-empty the data in that table)

which will NOT require CREATE TABLE permissions.

Kristen

enak
Starting Member

34 Posts

Posted - 2006-08-02 : 11:40:16

Kristen,

Thank you very much. That worked perfectly. However, now I am thinking that I need to use a temp table or view because there will be multiple people using the reports at the same time.

This object should only be available to the user that is running the report.
What do you suggest and how would I create it?

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 11:55:42

You can create a temporary table that will only be available in the current "connection" - so it may not last long enough to be any use to you!

Prefix the table name with "#" to create a temporary table, only visible to the current connection (i.e. multiple concurrent connections can all have the same "named" temporary table).

SELECT Applicant.client_num, Interview.*, Applicant.sub_name,
Applicant.client_name, Applicant.lastname, Applicant.firstname
into #PrintInterview
FROM Applicant, Interview
where Applicant.file_num = Interview.file_num and Applicant.file_num = 2720706

Or you could add a column for the User's Name, and include that in you queries:

DELETE [PrintInterview] WHERE [OwnerName] = 'MyName'

(assuming the new [OwnerName] is the first column then for example:)

INSERT INTO [PrintInterview]
SELECT 'MyName', Applicant.client_num, Interview.*, Applicant.sub_name,
Applicant.client_name, Applicant.lastname, Applicant.firstname
FROM Applicant, Interview
where Applicant.file_num = Interview.file_num and Applicant.file_num = 2720706

Note that you should use a column-list in the insert statemnet, rather than relying on the ordering of the columns!

Kristen

enak
Starting Member

34 Posts

Posted - 2006-08-02 : 12:07:35

Perfect. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources