Solved

sql server user permissions

Posted on 2015-01-21
11
199 Views
Last Modified: 2015-01-21
Hi - I have couple of databases configured as below

DBA1
Users:   admin1, admin2, admi3

DBB1
Users: user1, user2, user3

now I want to access data from DBA1, so I gave user1 read only permissions to DBA1
and i got the issue fixed, but that DBA1 refreshes every day and i need to assign that user1 read only permissions daily.

so I worked around and created a view in DBB1 for table in DBA1.
but i run the query i get error saying user1 does not have access to DBA1.table1

I thought when i created a view in second database DBB1 for a table in first database DBA1 my issue will be fixed.
but it did not - so how can I fix this issue.

Thanks,
0
Comment
Question by:shragi
[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
  • 3
  • 3
  • +1
11 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40562277
Every time you refresh DBA1 you need to run the following command:
EXEC sp_change_users_login 'Auto_Fix', 'user1'

Open in new window


It's only a matter of orphaned users and that SP will fix it for you.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40562282
Forgot to tell you that the command should be executed on the DBA1 context:
USE DBA1
GO
EXEC sp_change_users_login 'Auto_Fix', 'user'

Open in new window

0
 

Author Comment

by:shragi
ID: 40562309
well I am not doing the DBA1 refresh, it is done by admin's and I am not sure how will they react to that.
So is there another way, I mean can't I give some explicit permissions to user1 saying don't ask about permissions that needed to run the view in second database.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:shragi
ID: 40562312
also  sp_change_users_login  will be removed in future versions of sql server.
https://msdn.microsoft.com/en-us/library/ms174378.aspx
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40562319
can't I give some explicit permissions to user1 saying don't ask about permissions that needed to run the view in second database.
No. Problem is when the 2nd database is refreshed (DROP & CREATE) for him don't exist a 2nd database anymore and that's why the user got orphaned and that SP was built by Microsoft thinking on these kind of situations.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40562332
also  sp_change_users_login  will be removed in future versions of sql server.
Don't worry with that. Only when they say that is deprecated is when you should think in something else. Even when it's deprecated they only get rid of it two versions after. So imagine that will be deprecated in SQL Server 2016, so only when SQL Server 2020 will be released that won't work (counting that every 2 years we'll have a new version of SQL Server).

Anyway they should replace that SP with some other one ;)
0
 
LVL 25

Expert Comment

by:jogos
ID: 40562356
What do you mean by refresh DBA1?

If it is comming from another database (restore,  recreate...)  then the security is used from the source. So your authorisation must be fixed ther so with each refresh it  is present.   But then sitll the action Victor proposed can be necessary.

Best is to add the give security-action to the refresh process.
"and I am not sure how will they react to that." .. that is security or it is allowed and you must take actions to keep it allive or it is not allowed.  Seems to me a exact task for a dba to solve.
0
 

Author Comment

by:shragi
ID: 40562370
I used it and not sure what it did.

USE DBA1
GO
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'pwd'
GO

I executed the above sp and did not see user1 in the DBA1/Security/Users in SSMS
so what exactly did it do ?

Thanks,
0
 
LVL 25

Expert Comment

by:jogos
ID: 40562371
also  sp_change_users_login  will be removed in future versions of sql server.

USE DBA1
GO
Alter user  user1 with login =user1

Open in new window


And what it does.  Link the security given to user1 in your DBA1 again to the login in your instance. When is that needed? When you  restore a database to another instance and want the security for that to work on the new instance.    Not always needed when the logins are compatible.

And that is why I said put it in the source and maybe the action Victor propossed can still be needed.   Without puting it in the original db then the action does nothing because user1 is not found.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 40562397
If the problem is that the user is out of sync, you still shouldn't use that procedure any more as it is obsolete.  But even if you do use it, you should NEVER use "Auto_Fix", use "Update_One" instead.  I don't know of any DBA that wants SQL automatically creating logins, let alone for obsolete users(!).

The most permanent fix would be to adjust that's login sid on the current box to be the same as it is on the instance that DBA1 comes from.  It's a bit more work up front, but then it works from then on without running any additional commands.
0
 
LVL 25

Accepted Solution

by:
jogos earned 167 total points
ID: 40562499
All the user1-fix access is only necessary when that user is also existing and has reason to exist on the other location.....  and  then we already guess that database is comming from other location.
Still answer on question 'what do you mean by refresh db'  is key on how to get the security in order.    When db is reacreated and gets filled during day then the create of database must be alteredto add security for user1.  

And Scott explaned what I meant with compatible users, it is the sid of the login that mus be the same for not having to fix the security between instances.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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