Hello all,
I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:
USE [DBName]
GO
EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO
USE [master]
GO
EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO
CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBName]
GO
EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO
ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO
EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO
EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO
Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.
Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.
This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.
Am I on the right track? Or is there another way to automate the adding of Logins?
Thanks in advance,
DSXC
Just an update.
I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.
Is there another table that actually enables the login?
DSXC
|||I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.
Lo and behold... it works!
Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!
EDIT: I didn't mention the command line to run it...
SQLCMD.EXE -i MYSCRIPT.SQL
Hope that helps.
DSXC
|||Hi DSXC,
Sorry to have missed this thread earlier, I can shed some light on what you're seeing.
SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.
SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.
Mike
|||Hi Mike,
Thanks for your response. It makes a bit more sense now.
I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.
Again thanks for your response.
DSXC
