Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Assign Master DB User to another DB in SQL Azure

Posted on 2014-02-04
5
Medium Priority
?
361 Views
Last Modified: 2014-11-12
I have an Azure dB server that contains a login name [xUser] in the Master dB. I need to grant this account on another database, so I can set up grants for stored procedures using that account. However, when I try to create the user on the new db

CREATE USER xUser FROM LOGIN xUser;

Open in new window


I get this error:

**Msg 15007, Level 16, State 1, Line 1
'xUser' is not a valid login or you do not have permission.**

Open in new window


Needless to say, the 'xUser' account is the admin account for this Azure setup, and I'm logged in with that account, so I'm sort of confused as to how I wouldn't have permission to add the user to the database.

I've referenced these sites to make my attempts:

http://blogs.msdn.com/b/sqlazure/archive/2010/06/21/10028038.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/ee336235.aspx

I can't see any way in Azure to actually see the users in the masterdB, unless I connect to the database through SSMS - which in turn doesn't allow me the ability to see properties on the users, or do anything with them (They become unclickable items in the Object Explorer).

All I want to do is take [xUser] that is in the master dB, and assign it read/write/execute in my actual data dB.
0
Comment
Question by:Paul Kahl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39833351
You must connect with a SQL Admin account to that SQL Server via SSMS so you can check if the user exists under the SQL Server security tab - not master or another database security tab.
Then assuming that login exists in SQL you must be able (as and admin) to run the CREATE USER statement posted above.
0
 
LVL 3

Author Comment

by:Paul Kahl
ID: 39839374
Absolutely right, but doesn't solve the problem. The user, "xUser", is in the Master dB, as stated above. And it's the Admin account I have to do things in the database, such as create tables, create stored procedures, etc. It just, apparently, doesn't have the rights required to create new users.

I've checked the rights on "xUser", and it's created with DBO as it's default schema. This is the build script generated when I right click and do a "Generate Create Script" in SSMS:

USE [master]
GO

/****** Object:  User [xUser]    Script Date: 2/6/2014 8:39:55 AM ******/
CREATE USER [xUser] FOR LOGIN [xUser] WITH DEFAULT_SCHEMA=[dbo]
GO

Open in new window


It's a DBO account, and I'm logged in as it, but DBO doesn't have permission to create users.
0
 
LVL 3

Author Comment

by:Paul Kahl
ID: 39839661
-- ===============================================
-- Create SQL Login template for SQL Azure Database
-- ===============================================

-- Create the Login in Master
Use master
GO
CREATE LOGIN [xUserLogin] WITH PASSWORD = 'P@$sW0rd' 
GO

--Create the User in Database
USE theDatabase
GO
CREATE USER [xUser] FROM LOGIN [xUserLogin];

--Give user Rights - done in the Database also
USE theDatabase
GO

EXEC sp_addrolemember 'db_datareader', 'xUser';
EXEC sp_addrolemember 'db_datawriter', 'xUser';

Open in new window


So, this works to create the user and set them up... but then I can't log in with the user to gain access to the database. The password was put on the Login, not on the User, so I'm not sure how to structure the connection string to operate correctly.

<add name="ConnectionString"   connectionString="Data Source=dbServerName; Initial Catalog=theDatabase; User ID=xUser; Password=P@$sW0rd" providerName="System.Data.SqlClient" />

Open in new window


... Doesn't work, because there is no password associated with the user.
0
 
LVL 3

Accepted Solution

by:
Paul Kahl earned 0 total points
ID: 39874696
The answer turned out to be a combination of user creation and configuration in application:

-- ===============================================
-- Create SQL Login template for SQL Azure Database
-- ===============================================

--Create Login in Master
USE master
GO
CREATE LOGIN [xUserLogin]
	WITH PASSWORD = 'P@$sW0rd' 
GO

--Create User in Database - in this instance, xUser
USE [xDB]
GO

CREATE USER [xUserUser] FROM LOGIN [xUserLogin] WITH PASSWORD = 'P@$sW0rd' 
GO

--Give user Rights - done in specific DB
USE [xDB]
GO

EXEC sp_addrolemember 'db_datareader', 'xUserUser';
EXEC sp_addrolemember 'db_datawriter', 'xUserUser';

--Grant Rights to that user
GRANT EXECUTE TO [xUserUser]

Open in new window


and

<add name="ConnectionString" connectionString="Data Source=azureDB.database.windows.net,1433; Initial Catalog=xDB; User ID=xUserLogin; Password=P@$sW0rd" providerName="System.Data.SqlClient" />

Open in new window

0
 
LVL 3

Author Closing Comment

by:Paul Kahl
ID: 39885095
In the end, more research, and trial and error, turned up the answer. This was not a solution provided, but a solution discovered.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question