Hi Please solve this error...

Question

Hi Please solve this error...

anshu84onnet

SSC Eights!

Points: 836
More actions
May 6, 2010 at 11:14 pm

#140254

Hi i am log in through windows authentication as administrator but can not access the one user database getting this error
The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)
all services are running fine but main thing is that i can access that database with sa authentication. please solve this problem thanks in advance..

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Sounds like your windows auth hasn't been granted permissions to that database. Verify that you have the correct default database set and that your windows login has permissions to the database in question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

anshu84onnet SSC Eights! Points: 836 More actions · Answer 2

thanks for your support but please give me exact script solution if possible

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You should be able to verify everythhing Jason indicated using SSMS without having to run any queries, just the gui interface.

You can check the login under the Security folder of the server in Object Explorer. Any problems, use Books Online, the SQL Server Help System. You can access it by simply pressing the {f1} while in SSMS.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

I would just verify it via the GUI.

Login under your sa account and check the logins. Lynn has given some good resources for finding that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

anshu84onnet SSC Eights! Points: 836 More actions · Answer 5

now my msdb db and model database is not showing in system database list . and these three logins are present

1--BUILTIN\Administrators

2--HHH-5DFJV6HTEAZ\Administrator

3--sa

dont know what happened please give valuable suggestion does need to create new login.. and how to recover system database..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

May 6, 2010 at 11:42 pm

#1163806

Are you the DBA for the system in question?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Is your windows auth account a member of the builtin administrators group?

Have you verified what permissions you have for your windows auth account?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

newbie2sql SSC Eights! Points: 987 More actions · Answer 8

Hi Anshu,

As per your current scenario you will be able to login to the box if you are administrator on the box. To check out whether you login is part of administrator group on the box run the below command in the cmd window in the box.

net localgroup administrators.

If your windows loginid is reflecting then you should have access. Otherwise login through sa and execute the commands on the master db

use [master]

go

create login [mydomain\myloginid] from windows with default_database=[master],default_language=[us_english]

go

Above script will create a login for you and you can give access to that databases. But you want your windows login id as a admin of sql server execute below cmd:

exec master..sp_addsrvrolemember @loginame=N'[mydomain\myloginid]',@rolename=N'sysadmin'

go

and now try to login using windows authentication and you should have access.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

newbie2sql (5/7/2010)
Hi Anshu,
As per your current scenario you will be able to login to the box if you are administrator on the box. To check out whether you login is part of administrator group on the box run the below command in the cmd window in the box.
net localgroup administrators.
If your windows loginid is reflecting then you should have access. Otherwise login through sa and execute the commands on the master db
use [master]
go
create login [mydomain\myloginid] from windows with default_database=[master],default_language=[us_english]
go

This will generate an error since the user is already created.

exec master..sp_addsrvrolemember @loginame=N'[mydomain\myloginid]',@rolename=N'sysadmin'
go
and now try to login using windows authentication and you should have access.

Adding to the role is one method, however should come with advice. The OP needs to learn how to verify permissions not just overgrant permissions to everything.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 10

Lynn Pettis (5/6/2010)
Are you the DBA for the system in question?

I think answer to this question is most pertinent before providing any further feedback.