Test user login - 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

General SQL Server Forums

New to SQL Server Programming

Test user login

Author

Topic

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-14 : 20:27:36

I created a database role that have access to certain tables only, and created a user(login) with that role.

How to test the login if the access right is granted successfully?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-03-14 : 21:59:19

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-14 : 22:45:32

can I do that in SQL Query Analyser? such as connect xxx, or something like that

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-14 : 22:49:54

oh, I found out how to do that. Thanks for your hints

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-15 : 01:57:13

I created a dummy user a/c which has NO Access to Table A and B.

And I have a DTS that will delete all records in Table A, then insert the records selected from Table B.

Here comes the question:
I connect as the dummy user and execute the above DTS. Records in Table A are successfully deleted, but failed when trying to insert records.

Do you guys know why will have this result? I expect the dummy user will encounter error right away when executing the DTS, before the records being deleted Table A

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-15 : 08:06:23

Does the "DTS DELETE" use DELETE or TRUNCATE???....can't see why the security model fail but it might depend on the delete method?

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-15 : 22:45:39

I only have one simple delete statement in the Execute SQL Task:

Delete from Table A

I have no idea why the dummy user can execute that statement as she doesn't have access to Table A

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-15 : 23:09:54

I have 3 database on the same SQL server, SalesDev, SalesUAT, and SalesLive.
The dummy user(dev_user) has access to the SalesDev database only.

When I login as dev_user, I can view the objects under SalesDev database but not those under SalesUAT and SalesLive; as I expected.

Then I tried to create a DTS and encountered "login fails" error when select SalesUAT or SalesLive as the datasource, this is a correct result.

So, it seems the setting of dev_user is correct. But why she can execute the package that access the tables under SalesUAT database

? Can somebody kindly help me on this

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-16 : 12:46:37

Any chance this user is part of another group (at domain level?) which has delete rights???

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-20 : 00:44:57

I create this user and assigned only Public and dev role to her. Both cannot access UAT database

I cannot explain this case well to my supervisor....

Kristen
Test

22859 Posts

Posted - 2006-03-20 : 12:50:54

Does

sp_helprotect 'MyTable'

[note only one "p"!!] shed any light on what permissions are (collectively) available for that user and her applicable roles etc?

You can also do:

sp_helprotect 'MyTable', 'UserName'

if you are not logged in as that user.

Kristen

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-20 : 20:59:32

Result of sp_helprotect of the table:

Grantee  ProtectType Action  Column
dev_role  Grant         Select  (All+New)
publie    Grant         Select  (All+New)

User dev_user is created in Dev database for testing only, and she has NO access rights to the UAT database. However, she's now able to delete records in the UAT tables, by executing a DTS package, that's what I can't explained

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-20 : 21:09:52

quote:
Originally posted by nicole

I created a dummy user a/c which has NO Access to Table A and B.

And I have a DTS that will delete all records in Table A, then insert the records selected from Table B.

Here comes the question:
I connect as the dummy user and execute the above DTS. Records in Table A are successfully deleted, but failed when trying to insert records.

Do you guys know why will have this result? I expect the dummy user will encounter error right away when executing the DTS, before the records being deleted Table A

Which user created the DTS package ? Is it the dummy user ?
Can you elaborate how did the dummy user execute the DTS ? Via Enterprise Manager - DTS - Execute Package ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-22 : 21:02:30

The package is created by "sa", NOT the dummy user.

Yes, the dummy user executes the DTS Via Enterprise Manager - DTS - Execute Package. Is it related?

Thanks for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 21:04:13

What is the user id in the DTS Package connection object ? sa ? or the dummy user ?

The connection object will be connected using that user specified in the DTS Package

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-23 : 00:21:38

"sa" is used to connect to the database in the package.

If the dummy user can execute the package because the package is using "sa" to connect to the database, I don't understand why the execution of the package failed after processing the first task.

The package will delete all records in Table A, then insert the records to Table A again by selecting records from Table B.

My case is:
Records in Table A are successfully deleted, but failed when trying to insert records.

I really don't understand.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 00:31:26

is both the DELETE from TableA and INSERT from TableB of same package or different package ?

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-23 : 02:03:19

Both delete and insert are in the same package. A workflow is added between these 2 actions, if delete success, then insert records.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-03-23 : 04:37:44

What error was generated on the insert? Are there any constraints on Table A?

steve

-----------

Oh, so they have internet on computers now!

nicole
Yak Posting Veteran

97 Posts

Posted - 2006-03-23 : 21:19:45

Strange... Strange... Strange...

Action:
Login as dev_user and execute a package from the Enterprise Manager (DTS --> Execute Package).

Below error prompt when executing the package from the Enterprise Manager installed on my PC.
[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specified

But If I login as "sa", the package can be executed successfully.

Then, I repeated the above action on the Enterprise Manager installed on the database server, again, the package can be executed successfully.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources