Security issue - 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

Security issue

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2007-05-09 : 04:22:56

We have an application which has SQL server 2005 as the back end. When I log into the application and try to perform certain operations I get an error message saying:

'You must either be a Security administrator with db_owner access to the database or a System administrator to add a user.'

I can't understand why I get this message as I have given the user login membership in all of the available roles as a work around. I also get the same error message if I login using the 'sa' account.

I'd really appreciate some help on this.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 04:36:18

Which login credentials your application uses to connect to SQL Server? Can you post the connection string here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chris_cs
Posting Yak Master

223 Posts

Posted - 2007-05-09 : 04:39:49

The application is provided to us so I'm not 100% sure about the connection string.

What I can't understand though is why I get the error message when trying to login using sa. The sa user is the dbo and is the highest level of user.

chris_cs
Posting Yak Master

223 Posts

Posted - 2007-05-09 : 06:35:03

Right I have doule checked the permissions on the database but I cannot see why I'm unable to add the user through the application. The sa user is the owner of the database.

Under the server roles the sa user has public and sysadmin selected. In User Mapping sa also has db_owner and public selected for the database in question.

Is there anything I'm missing here as this is driving me nuts!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-09 : 10:51:59

Can you add user in SSMS?

chris_cs
Posting Yak Master

223 Posts

Posted - 2007-05-10 : 04:48:57

If I log in as sa I can yeah.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 04:58:15

I think you require to check through which user your application is connected to the Application.
And then accordingly give the rights to that user.

Since the Application will be using completely different set of different connections

you can login to your application and run the following query you will come to know which user your application is taking for logining to the SQL SERVER


SELECT Hostname 'Connected User', db_name(dbid) 'Database', Program_Name, Count(Program_Name) 'Connections'
,Loginame
FROM master.dbo.sysprocesses Where HostName <> '' And db_name(dbid) <> 'master' 
Group By Hostname, db_name(dbid), Program_Name,loginame

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources