Solved

sql server user permissions

Posted on 2015-01-21
11
173 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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 45

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 45

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
 

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 45

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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 45

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:ScottPletcher
ScottPletcher 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now