sysadmin to dbowner

Question

sysadmin to dbowner

manoj-491873

SSC Enthusiast

Points: 112
More actions
December 30, 2007 at 11:37 pm

#185235

hi
what is the permission required to access sql server 2005 from client system.Every one havin sysadmin permission to access database.
If i remove the sysadmin permission and assign dbowner it displays error.
and user default database is master .
i dont known how to reslove this problem.
can any one help me on this issue
regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Minimum permissions needed to access the server is CONNECT on the server. I think you get that by default on any login, though it can be revoked.

Change the user's default database to the DB they need access to. Remove sysadmin, and db_owner and grant then must the permissions they need in that database.

If you're still getting an error, please post the error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manoj-491873 SSC Enthusiast Points: 112 More actions · Answer 2

thanks for reply,

if i remove the sysadmin permission and i assinged db_writer and db_reader.but still they are getting error like.

cannot open database ''abs_tr'' requested by login . The login failed for user ohk\jeva'

and also one user access morethan one database ...

regards

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It looks like ''abs_tr'' is the default database for that login. Is that correct? Is it the DB you gave them data reader and data writer in?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

From object explorer, expand out security and logins, find the login ohk\jeva, right click, select properties, go to User Mapping

Make sure that the login is mapped to the DBs that it needs access to. This creates the users in the specific databases.

Then you can go to the specific databases and grant the required permissions to the users.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manoj-491873 SSC Enthusiast Points: 112 More actions · Answer 5

hi ,

everyone is having master database is default one.

if i assign someother database to that user .He was accessing morethan 2 or 3 database.

we can assign only one database as default one .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

A non-admin user's default database should not be master. Set the database that the login uses most frequently as the default.

Other thing to check is the connection string that the applicationj uses. It may be that the connection string specifies a default catalog that the login does not have rights to.

If that's the case, you've got the choice of giving the login rights in that database, or getting the connection string changed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

manoj-491873 SSC Enthusiast Points: 112 More actions · Answer 7

hi,thank for reply

i think for all applications there is only one connection point they given.

and also for all databases there is one database master_db (not sysdatabase) which will consists of all login details through this login the users are accessing.

con.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Atab;

Data Source=10.1.2.13"

'mascon.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master_db;Data Source=10.1.2.13"

response.write("Connected")

%>

the above one is application connect string . i dont known the applicaition part. iam newly joined.

regards,

manoj

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

con.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Atab;

This connection string requires that the login has access to the DB Atab. If it doesn't, you'll get the error message you described earlier.

The second connection string looks like it's commented out (in VB a single quote denotes a full line comments, like -- in SQL)

You'll have to take this up with the application developer, fnd out why the connection string is like that, and not to master_db.

Or, if the login should use Atab, then make sure that it has whatever rights to that database that it needs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass