• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

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.
0
CalBob
Asked:
CalBob
  • 5
  • 3
  • 3
  • +1
1 Solution
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
CalBobAuthor Commented:
I explain in the response that I figured it out and included a screen shot.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now