question

Iamethrethoyug avatar image
Iamethrethoyug asked

Granting permission to create user for Windows admin account.

I am logged in via Windows Authentication using the Windows administrator account. I tried the following code to create user. It fails, stating the error of "'iwantsql' is not a valid login or you do not have permission." create database testa1; go use testa1; go create user iwantsql; Next, I read that a server login must be created for the Windows admin account before it can create a user. So I created a login, but it fails. create login TESTDB\Administrator from WINDOWS Now an error of "Incorrect syntax near '\'." appears. I am dumbfounded. How do I proceed from here ? Pardon me if my concepts are totally wrong.
tsqlpermissionserror-messageusercreate
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.

Dugi avatar image
Dugi answered
According to your explanation, you are trying to create user (iwantsql) on DB without Login. You can use your user 'iwantsql', creating user without Login (for SQL Server 2005 ++), like: USE YourDB GO CREATE USER [iwantsql] WITHOUT LOGIN GO If you need to map it with your WindowsUser, you need to create login use for SQL Server or even from the windows like you describe above, but during the creation of your DB you need to create it as user --Create Login from Windows with default DB: CREATE LOGIN [ServerName\WinUserName] FROM WINDOWS WITH DEFAULT_DATABASE=[YourDB] GO --Create User for Login USE YourDB GO CREATE USER [ServerName\WinUserName] FOR LOGIN [ServerName\WinUserName] GO Hope it helps!
2 comments
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.

Thanks for the idea about 'WITHOUT LOGIN' . Hmm I am currently logged in as TESTDB\Administrator , but I can't create login for myself or any other user, and also can't create user.
0 Likes 0 ·
Is the "Windows administrator account" also a sysadmin for the sql server instance?
0 Likes 0 ·
Dugi avatar image
Dugi answered
TestDB\Administrator (TestDB - should be your server-name not database name) and Administrator should be your Windows UserName! - hope it's clear to you!
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.

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.