SQL Server Security/Permissions

I just installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  I set it up to use Windows authentication.  I'm having trouble with what seems to be very simple DB permissions.

I am the administrator for the laptop and have sa rights for the instance of SQL Server.  

Under the SECURITY > Logins tree of the instance my login is shown (i.e. MYLAPTOP\CalBob).  If I right-click on the properties of my login I have user mappings to the db (i.e. ANALYTICS) I need access to.  However, when I go to the db, ANALYTICS  >  Security, my User name/account is not listed, and when I try to add it, I get one of two error messages telling me:

1. If I try to add MYLAPTOP\CalBob I get message saying it is invalid because it contains invalid characters.
2. If I try  to add CalBob it says CalBob is an invalid login or I don't have permissions.

Ultimately the problem is if I want to give myself permissions to a table in that database I'm told the user does not exist.  For example.

GRANT SELECT on tablename1 TO CalBob

I receive an error indicating the user doesn't exist or I don't have permissions.  And I understand that CalBob doesn't exist as a User in the database, but I can't add CalBob either.

This seems simple but I'm obviously missing something.  Any ideas?

Thanks.
CalBobAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BOFH_JohnCommented:
In Server>Security>Users [Logged_in_user]>Properties>Server_Roles, do you have the SysAdmin role?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have sa rights you don't need to give permissions to your user for each database. It already has it implicitly.
0
ZberteocCommented:
But what was the problem? You didn't have access to the database? You never mentioned what the problem was just the fact that under the database security tab your account was not there.

If that is the case, you can't access the database, it means that your account MYLAPTOP\CalBob does NOT have sa(sysadmin) permissions. In that case you cannot change your role to sysadmin using that account.

If, however, your account has sa(sysadmin role) permissions then you don't have to worry what is under the Security tab for that database. The sysadmin role gives you access for ANYTHING on that SQL server instance.

My guess would be that your account has sa permission but you tried to add it to the database Security as well, which is not needed at all. But when you try to add MYLAPTOP\CalBob you cannot have a database user with the \ character in it and when you tried to add CalBob only the error was that a login with that name doesn't exist, which is true, and is not needed either. The fact that the message has "or you don't have permission" doesn't apply here, it is only Microsoft trying to kill 2 rabbits with one shot.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

BOFH_JohnCommented:
@ Vitor Montalvão:
The OP stated that SQL Server has been installed and configured to use integrated windows authentication. If the installer was executed using "Run as Administrator" then the OP's UserID would not automatically have the SA Role assigned as the installer would have assign it to MACHINE\Administrator. The OP would have to log into the SQL Server instance as Administrator and grant himself SysAdmin before he would have the authority to make the changes.
0
CalBobAuthor Commented:
Yes I do.
0
ZberteocCommented:
Yes, you do what?
0
CalBobAuthor Commented:
I was answering BOFH_John.  I do have the sysadmin role.  The real problem is when I deploy a report to Reporting Services (on the same box) I get an error stating "cannot create a connection to data source..." which is the account in question.  Since I can't figure out why that is the case, I noticed my account isn't listed within the security of that database, and since I couldn't add it, I figured that was the problem.  Maybe it isn't.  I'm stumped.
0
ZberteocCommented:
If your account has the sysadmin role then the answer to your question is in the last paragraph in my first post.

The issue with the Reporting services is a total different issue and you will have to open a new question. My guess is that the connection as defined in the report itself has something wrong in it and it is not related to your account. It may be a bad connection string, a connection imported from previous server/database/report where it was defined differently. As I said, a different issue.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
 The real problem is when I deploy a report to Reporting Services (on the same box) I get an error stating "cannot create a connection to data source..."
A login in your SQL Server instance doesn't have automatically access to your Reporting Services, even if they are installed in the same box. You'll need to create a login for the Reporting Services as you've created for the SQL Server.
0
CalBobAuthor Commented:
I'm mulling this comment.  I appreciate all the comments.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
CalBob, a feedback will be appreciated.
Cheers
0
CalBobAuthor Commented:
My apologies for the delayed response.  I was thinking I would get an email indicating there was a response.  When I didn't see one I didn't bother to check.

Anyway, I found out the permissions advice I was getting from all of you was correct but that wasn't the problem/solution.  I found out that SSRS 2016 is set up slightly different in terms of data sources in Report Manager.  If you are using a Windows account (or Windows Authentication) you have to select it from a drop-down list.  In earlier versions, there was a checkbox under the credentials you enter - so I missed the change.  

I have included a screen shot of the change and solution for me.  Now it seems simple.  Thanks for you suggestions.
ScreenShotSnipet.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CalBobAuthor Commented:
I explain in the response that I figured it out and included a screen shot.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.