question

Hibiscus avatar image
Hibiscus asked

SQL Server 2005 permission denied to create login, user?

I installed SQL Server 2005 and selected windows authentication for the server instance I have.

Later on, when I imported a database from a .bak file into the server instance which use SQL authentication to allow certain user access. So I couldn't view the important database with login with my windows authentication. I tried to create a new login or new user, and failed due to permission denied.

I am wondering how I can create a login of SQL authentication when I am a windows authentication login user. And how can I create a user also?

Thanks for your help.

loginuser
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

Using some of the recent versions of Windows, you may find that account needs to run SSMS with elevated permissions to access the sysadmin role.

So try right-clicking on the SSMS shortcut, and saying "Run as administrator", so that you adopt all the rights you need.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

Are you a systems administrator on SQL Server? If you are, then you should automatically have permission to access the database unless your login is somehow listed with explicit deny permissions. Even if it is listed with explicity denies, you can remove those if you are the systems administrator.

If you are not a system administror on the SQL Server instance, then your best course of action is to ask the person who is to give you permissions on that database.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hibiscus avatar image
Hibiscus answered

Thanks for your response.

The windows user I am using to run SQL server is admin user. But sys.syslogins list sa as the only admin, another record is for 'BUILTIN\USERS' and is not sysadmin.

My problem is that I don't know what the password is for sa and I never set one. when I use blank password, I can't login with sa. ANd I don't know how to get windows admin user to have sysadmin priviledge for this server instance.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hibiscus avatar image
Hibiscus answered

Thanks Rob. That solves my problem.

I was trying to flag it as the answer for quite a while and I was not able to do that since it asks some reputation which I don't have as first time user.

I really appreciate this wonderful forum. And thanks everyone for answering my question.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hibiscus - You just click the tick at the left to accept an answer, you don't need any rep for that
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.