Solved

sql server user permissions

Posted on 2015-01-21
11
187 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 50

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 50

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 50

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 50

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

739 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