x

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.

more ▼

asked Apr 05 '10 at 09:13 PM in Default

Hibiscus gravatar image

Hibiscus
34 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Apr 06 '10 at 12:50 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

Or, on Win7, we have two accounts, one that is admin for our servers and our regular account, so we have to shift-right click and select run as different user. Same thing though.
Apr 06 '10 at 08:28 AM Grant Fritchey ♦♦
Yeah - a few ways to skin that cat. Another option is to make the shortcut actually use runas ..., so that you can just click it...
Apr 06 '10 at 10:43 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 05 '10 at 09:16 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 06 '10 at 12:55 AM

Hibiscus gravatar image

Hibiscus
34 1 1 1

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 06 '10 at 01:07 AM

Hibiscus gravatar image

Hibiscus
34 1 1 1

hibiscus - You just click the tick at the left to accept an answer, you don't need any rep for that
Apr 06 '10 at 03:21 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x81
x30

asked: Apr 05 '10 at 09:13 PM

Seen: 2920 times

Last Updated: Apr 05 '10 at 09:13 PM