Updating Database User SID in SQL 2005
Hi all
We're currently undertaking the development of our first SQL Server 2005 database and we've stumbled across a problem with our method of deployment from Development through System Test, UAT and, finally, to Production.
Under SQL Server 2000 we would build a new database in Development and assign permissions to a specific database user which was linked to an appropriate Windows Login - the whole build process was scripted. We would then hand the mdf and ldf files over to the Operations team who would attach the database to the System Test server, create a new Windows Login (appropriate for the environment), then run a script which would update the SID stored against the database user to the SID of the appropriate login.
e.g.
Database user (consistent through all environments) = AppDAL
Development: Windows Login = DOMAIN\AppDEVDAL
System Test: Windows Login = DOMAIN\AppSYSDAL
UAT: Windows Login = DOMAIN\AppUATDAL
Production: Windows Login = DOMAIN\AppLIVEDAL
The script, which directly updates the dbo.sysusers table in SQL 2000, links the AppDAL user to the appropriate Windows Login.
Is there any way to perform this task under SQL Server 2005? I'm aware of the sp_change_users_login stored proc, however this only works for SQL Server logins and not Windows logins. Also I'm aware that direct updates to system tables are not allowed under SQL Server 2005 and were not recommended under SQL Server 2000 so we were actually using an unsupported method during deployment.My guess is that we will have to change our method of deployment and rely on the Operations team to build the database in each enviroment from scripts, however I was wondering if there is another way before we go ahead.
Interestingly SQL Server 2005's sp_change_users_login stored proc contains SQL that does not run outside of the stored proc:
EXEC
%%UserOrGroup(Name= @UserNamePattern).SetSID(SID= @loginsid, IsExternal= 0, IsGroup= 0)SQL Server 2005 will not allow creation of a new stored proc that contains this SQL, wrapping the code in EXEC('') causes an error during runtime.
Is anyone familiar with the above syntax? Searching on Google has been fruitless.
Thanks
Chris
Unfortunately you are right; sp_change_users_login only works for SQL principals and not for Windows principals. As you already described, the solution you relied on (modifying the system tables directly) in SQL Server 2000 is not recommended or supported.
You may be able to create all the objects on your database and just leave the user creation and permissions assignment for environment-dependent scripting.
If you have dependencies on the database user creation (such as creating objects on the default schema, etc.) you can use the new schema separation. For example:
-- Create the schema beforehand
CREATESCHEMA [AppDAL]
go
-- Create all objects needed on the schema
CREATETABLE [AppDAL].[myTable]( dataint)
go
-- ...
-
-- On the environment-sensitive script,
-- create the user and set the newly created
-- schema as the default schema for this principal
CREATEUSER [AppDAL]FORLOGIN [DOMAIN\login_name]
WITH DEFAULT_SCHEMA= [AppDAL]
go
-- Change ownership of the schema to the new user
ALTERAUTHORIZATIONONSCHEMA::[AppDAL]TO [AppDAL]
go
-- Grant any additional permissions on the new principal
-- …
This way you can minimize the changes needed to your current deployment system.
Regarding the second part of your question, some of the code used in SQL Server system modules is reserved for SQL Server system modules use only as it updates system tables while keeping metadata coherent, and it is not intended for user defined procedures.
Please, let us know if my suggestion above helped.
-Raul Garcia
SDE/T
SQL Server Engine
Hi Raul
Thanks for the response. It looks like we will have to change either our deployment method or security practices.
With regards to using Schemas, we may run into difficulties where we share tables between applications that use their own logins, and therefore their own database users.
For instance if App1 and App2 both require data from table 'MyTable' we would presently create a stored procedure for each application to access data from MyTable, then assign permissions on the stored procedure to the relevant application's database user. If my understanding is correct, we should, therefore, not assign an application-specific schema to MyTable. On this basis would you deem the following to be acceptable?
CREATE SCHEMA [App1DAL]
GO
CREATE SCHEMA [App2DAL]
GO
CREATETABLE [dbo].[myTable]( dataint)
GO
CREATEPROCEDURE [App1DAL].[spApp1Procedure]
AS
SELECT data
FROM dbo.myTable
GO
CREATEPROCEDURE [App2DAL].[spApp2Procedure]
AS
SELECT data
FROM dbo.myTable
GO
CREATEUSER [App1DAL]FORLOGIN [DOMAIN\login_name]
WITH DEFAULT_SCHEMA= [App1DAL]
go
-- Change ownership of the schema to the new user
ALTERAUTHORIZATIONONSCHEMA::[App1DAL]TO [App1DAL]
go
CREATEUSER [App2DAL]FORLOGIN [DOMAIN\login_name]
WITH DEFAULT_SCHEMA= [App2DAL]
go
-- Change ownership of the schema to the new user
ALTERAUTHORIZATIONONSCHEMA::[App2DAL]TO [App2DAL]
go
My concern with this approach is that as the App1DAL and App2DAL users are now schema owners then they would each be able to drop the stored procedures that belong to their corresponding schema. Is this correct? If so then this is a concern for us as our development standards dictate that we assign mininmal permissions on our database objects to our database users.
Thanks again
Chris
My mistake, I assumed that you wanted to make the user the owner of the schema as well. Schema separation not only allows you to change the schema ownership, it also allows you to specify a default schema (used for name resolution purposes) without implying any ownership relationship between the schema and the user.
Here is a revised sample without changing the schema ownership, hopefully it will be more useful:
CREATESCHEMA [App1DAL]
go
CREATETABLE [dbo].[myTable]( dataint)
go
CREATEPROCEDURE [App1DAL].[spApp1Procedure]
AS
SELECT data
FROM dbo.myTable
GO
-- ...
-
CREATEUSER [App1DAL]FORLOGIN [REDMOND\ruslano]
WITH DEFAULT_SCHEMA= [App1DAL]
go
-- instead of changing ownership, just grant permission
-- to execute any stored procedure on teh schema
GRANTEXECUTEONSCHEMA::[App1DAL]TO [App1DAL]
go
--
-- Now we can test that it works
EXECUTEASUSER='App1DAL'
go
-- Try to select the table directly, it should fail
SELECT*FROM dbo.myTable
go
-- Try to execute the SP
-- the execution should succeed for the following 2 reasons:
-- 1) This prinicpal has EXECUTE permission on the schema
-- 2) because the app schema & the table have the same owner
--onwership chaining is allowing the SP to select from the table
-- Notice that I am not specifying the schema,
-- it will use the default schema for this context ([App1DAL])
the defualtschema
EXEC [spApp1Procedure]
go
-- Try to drop/alter the SP, should fail
ALTERPROCEDURE [App1DAL].[spApp1Procedure]
AS
PRINT'Gotcha!'
SELECT data
FROM dbo.myTable
GO
DROPPROCEDURE [App1DAL].[spApp1Procedure]
go
-- Revert to self
REVERT
Go
In this small demo I am using ownership-chaining (OC), but for many applications OC is not an option; I would recommend to use digital sigantures on the module instead. Using digital sigantures will allow access to the tables/resources to any individual who can execute the module without granting direct permissions. Laurentiu has a very good article that talks about this new feature in his blog: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx
By the way, I forgot to mention that we are aware of the existing limitations for this scenario (remap a Windows DB user to a different login) and we are currently working on a solution for a future release of SQL Server, but I cannot make any comments or promises on when it will be available.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server Engine
Hi Raul.
It strikes me that the use of Schemas for this particular purpose can be compared to the use of Roles in SQL Server 2000. You've certainly given us a few ideas to work with.
Many thanks for your help and advice,
Chris
Hi,
We have a similar problem when moving a database from SQL 2000 to a new deployment in on 2005 with a different domain name where the widows principals get orphaned. We create the windows group/user as an new SQL login, but can't link it to the restored SQL database login.
Does this mean there is no way to update the database principal's SID to match the SQL windows principal login?
Thanks
Paul
Changing the mapping for windows DB users SIDs is not supported on SQL Server 2005 SP1, RTM or previous versions. As Chris mentioned, one mechanism commonly used in SQL Server 2000 to accomplish this task was to manually modify the system tables, but this mechanism has never been supported by Microsoft.
We are aware of this limitation and the problems it causes to our customers, and we are working on a solution that will be available as soon as possible, but unfortunately (as I mentioned earlier) I cannot make comments/promises on release dates or what features will be available on future releases.
I can suggest writing a script that can do the following:
- Change the ownership of any schema/object owned by the orphan Windows principal (you may create a user without login as a transition owner)
- Drop the orphan Windows principal
- Create a new Windows principal to replace the previous one
- Grant all proper permissions/add role memberships to this new principal
- Change all schema/object ownership to this principal
I understand the recommendations I provided above on this thread cannot solve all problems or help on all scenarios, but I hope they help to mitigate the problem until this feature is available.
Thanks a lot for your feedback.
-Raul Garcia
SDE/T
SQL Server Engine
Thanks,
Our problem was we are moving to a new domain setup (same windows name) - previous AD has suspected corruptions - and migrating to new SQL 2005 hardware at the same time. It was looking easy to fix the orphaned windows users based on the SQL 2000 posts, but it looks like we'll have to script out the permissions and reapply these after migration, luckily we only have about 30 databases.
Thanks for the help
Paul
