|
|
You're in the
sql server msde
group:MSDE across multiple profiles?
sql server msde:
discovered that if one user installs an instance of MSDE, then no other users who log into that machine can create databases. Is there a flag or installation setting I need to configure to get it to work for whomever logs into a machine? Cheers, Jon
hi Jon,
[quoted text, click to view] Jon Pope wrote: > Is there a way to get MSDE to work across multiple profiles? I've > discovered that if one user installs an instance of MSDE, then no > other users who log into that machine can create databases. > > Is there a flag or installation setting I need to configure to get it > to work for whomever logs into a machine? > > Cheers, Jon SQL Server does not rely on the user who installed the instance.. usually it is installed using administrators rights becouse of lots of implications regarding defining the services and the like, but this is not mandatory (but is a good policy :D) but if you install MSDE on a machine, when rebooting and connecting with a limited user account you can of course connect to the MSDE instance as long as the current windows accout has been granted login permission to the instance (if using trusted authenticated WinNT connections) or providing valid SQL Server credentials (for a SQL Server authenticated connection, with userid+pwd)... the services, SQL Server and SQL Server Agent (if running and so defined) will run under different accounts (usually LocalSystem) and they work just fine.. the problem, as already pointed out, regards the interactive connections... in order to be able to create new databases, the logged in account (both WinNT account for trusted connections, or the SQL Server login for SQL Server authenticated connections) must be member of the the dbcreator server role at least http://msdn2.microsoft.com/en-us/library/ms176014.aspx, one of the fixed SQL Server server roles (http://msdn2.microsoft.com/en-us/library/ms188659.aspx)... all Windows local administrators are member of a default SQL Server login generated at installation time, the Builtin\Administrators role, and they are member of the sysadmin server role, granting them all sort of permissions on the local instance, thus granting them permissions to create database, create objects in databases, access data and the like.. if you want everyone to log in SQL Server, you have to grant the corresponding windows account (for trusted connections) login permission creating a corresponding SQL Server Windows related account.. you can do that at the role membership granularity, that's to say granting all "Power Users", all "Users" and so on permissions to log in.. you do that via sp_grantlogin system stored procedure (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ga-gz_8dri.asp) [have a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003 as well] this is the very first security phase on SQL Server... as regard the second authentication phase, it regards specific access to each database, and this is done via the sp_grantdbaccess system stored procedures, that maps a specific server login to a particular database user, the second granularty security concern... this allow to to give specific permissions and privileges to each db user, granting him/her membership to specific db roles, granting/denying specific action on every db object and so on.. so please start reading at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_79lt.asp, the "logins" topic and the "users" topic.. -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.19.0 - DbaMgr ver 0.63.0 and further SQL Tools --------- remove DMO to reply
Jon,
I've also been dealing with this issue. The line of business app I've been working with has a central MSDE database on the server and also has distributed MSDE databases on each client. I am having problems on the client install and developed the following osql script to grant access to each domain user: USE master if not exists (select * from master.dbo.syslogins where loginname = N'<localcomputername>\<user>') exec sp_grantlogin N'<localcomputername>\<user>' exec sp_defaultdb N'<localcomputername>\<user>', N'<Target_Database>' exec sp_defaultlanguage N'<localcomputername>\<user>', N'us_english' GO USE <Target_Database> exec sp_grantlogin N'<localcomputername>\<user>' exec sp_grantdbaccess N'<localcomputername>\<user>' exec sp_changegroup 'db_owner', '<localcomputername>\<user>' GO Yes I know awarding db_owner is bad and I do not plan it for the server level. I also have an issue with the application vendor not providing a script to accomplish the grants at the object level. They seem to expect you to give everyone admin authority! However I was hoping to find a cleaner method so I wouldn't have to do this for each domain account login at each client. Andrea appears to be hinting that we can use the domain roles to accomplish this. However with the links provided I am unable to read far enough between the lines to see how this would be done. I am appreciative of any ideas (shooting the software vendor is not an option) and will continue to scour documentation. [quoted text, click to view] "Jon Pope" wrote:
> Is there a way to get MSDE to work across multiple profiles? I've > discovered that if one user installs an instance of MSDE, then no other > users who log into that machine can create databases. > > Is there a flag or installation setting I need to configure to get it to > work for whomever logs into a machine? > > Cheers, Jon > >
Andrea,
Would exec sp_grantlogin <domain>\Users work? and then continue the same theme throughout the script in the previous post? [quoted text, click to view] "Jon Pope" wrote:
> Is there a way to get MSDE to work across multiple profiles? I've > discovered that if one user installs an instance of MSDE, then no other > users who log into that machine can create databases. > > Is there a flag or installation setting I need to configure to get it to > work for whomever logs into a machine? > > Cheers, Jon > >
OK, I was able to get the domain group substituted in all of the script but
one spot. This was the sp_changegroup stored procedure. Andrea, any suggestions for this other than granting permissions on each object in the database to the group? Guess I'll have to call the software vendor help desk and ask if they used in stored procedures in the code (fat chance they'll know!) ;0) -R [quoted text, click to view] "Jon Pope" wrote:
> Is there a way to get MSDE to work across multiple profiles? I've > discovered that if one user installs an instance of MSDE, then no other > users who log into that machine can create databases. > > Is there a flag or installation setting I need to configure to get it to > work for whomever logs into a machine? > > Cheers, Jon > >
hi,
[quoted text, click to view] Randall Loftis wrote: usually it's best to install MSDE under administrator permissions... in this > OK, I was able to get the domain group substituted in all of the > script but one spot. This was the sp_changegroup stored procedure. > > Andrea, any suggestions for this other than granting permissions on > each object in the database to the group? Guess I'll have to call > the software vendor help desk and ask if they used in stored > procedures in the code (fat chance they'll know!) ;0) > case no additional task is required .. as regard access to specific object, then this is a "required" feature as this is the security mechanism directly built in SQL Server it self.. you added a big security concern, making each user a member of the db_owner database role.. no such a permission is actually required.. you should always follow the least privilege principle, valid in Windows account membership as well as in SQL Server world (http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/luawinxp.mspx).. usually a "simple user" only needs access to already created objecs, be their base tables (not that good :D), stored procedures, views and the like.. if you want a "very simple" permission path, just make your database users members of the db_datareader and db_datawriter builtin database roles.. this will grant them access in read write mode to all user tables defined in the relative database.. but the preferred path to go is to create a defined set of other user defined database roles.. you can so define, say, 1 "Managers" database role, another 1 "Accounting Employees" role, a "Help desk" role and so on.. each specific database user will then be made member of the relative database role(s)... going deeper, you'll grant specific permissions to each role (and not to users).. so you'll grant read write (AKA SELECT/UPDATE/INSERT/DELETE) permissions to the "Managers" role to all tables they require to manage, but you won't grant SELECT/UPDATE/INSERT/DELETE permissions to the "Help desk" role to accounting related tables... you can this way define the granularity of your permission schema(s)... but you already pointed out this is not the way to go... if you are by yourself in the way to find waht permission is required for each specific object than it's very hard.. yes, you are right, the vendor should provide a script or a gui tool to manager permissions for you, or you can only guess where rights are needed when you get exceptions instead of actual results... again, db_owner membership is not a panacea and should not be the way to go, but obviously works.. :( [quoted text, click to view] >USE <Target_Database> and you should use> exec sp_grantlogin N'<localcomputername>\<user>' <-- this is > redundant as already granted before > exec sp_grantdbaccess N'<localcomputername>\<user>' > exec sp_changegroup 'db_owner', '<localcomputername>\<user>' >GO EXEC sp_addrolemember 'role', 'user'; instead of sp_changegroup.. it's not that big issue as your user will only be member of the public database role, and can not be eliminated from it... but it's more correct :D another (very sad) way to accomplish a preliminary access (waiting for the vendor to provide the path) could be to grant permissions (for each object) to the public database role... each user is member of that role so this will grant everyone the given permission(s) to all objects.. remember that the "guest" database user (if not dropped already, I do it in the model database, so that no guest is actually allowed :D) will be granted permissions too.. be very carefull :D -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
Andrea,
Thank you for your timely response. I have been out of the dba role for several years and your short refresher is helpful. One item I am concerned about is the install in the Admin role. The install was performed under a Domain Administrator account and users did not have access to the database nor could they start the services. Further as I recall I did have to perform the master db sp_grantlogin or subsequent master db stored procedures would not work. When you refer to install as administrator do you refer to the local administrator account? In your previous reply your experience shows. You gave a standard enterprise level answer and then qualified it with exceptions we all run into such as specific situations with applications that may not be coded or designed properly. Again, Thanks! [quoted text, click to view] "Andrea Montanari" wrote:
> hi, > Randall Loftis wrote: > > OK, I was able to get the domain group substituted in all of the > > script but one spot. This was the sp_changegroup stored procedure. > > > > Andrea, any suggestions for this other than granting permissions on > > each object in the database to the group? Guess I'll have to call > > the software vendor help desk and ask if they used in stored > > procedures in the code (fat chance they'll know!) ;0) > > > usually it's best to install MSDE under administrator permissions... in this > case no additional task is required .. > as regard access to specific object, then this is a "required" feature as > this is the security mechanism directly built in SQL Server it self.. > you added a big security concern, making each user a member of the db_owner > database role.. no such a permission is actually required.. you should > always follow the least privilege principle, valid in Windows account > membership as well as in SQL Server world > (http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/luawinxp.mspx).. > usually a "simple user" only needs access to already created objecs, be > their base tables (not that good :D), stored procedures, views and the > like.. > if you want a "very simple" permission path, just make your database users > members of the db_datareader and db_datawriter builtin database roles.. this > will grant them access in read write mode to all user tables defined in the > relative database.. > but the preferred path to go is to create a defined set of other user > defined database roles.. > you can so define, say, 1 "Managers" database role, another 1 "Accounting > Employees" role, a "Help desk" role and so on.. each specific database user > will then be made member of the relative database role(s)... > going deeper, you'll grant specific permissions to each role (and not to > users).. so you'll grant read write (AKA SELECT/UPDATE/INSERT/DELETE) > permissions to the "Managers" role to all tables they require to manage, but > you won't grant SELECT/UPDATE/INSERT/DELETE permissions to the "Help desk" > role to accounting related tables... you can this way define the granularity > of your permission schema(s)... > but you already pointed out this is not the way to go... > if you are by yourself in the way to find waht permission is required for > each specific object than it's very hard.. yes, you are right, the vendor > should provide a script or a gui tool to manager permissions for you, or you > can only guess where rights are needed when you get exceptions instead of > actual results... > again, db_owner membership is not a panacea and should not be the way to go, > but obviously works.. :( > > > >USE <Target_Database> > > exec sp_grantlogin N'<localcomputername>\<user>' <-- this is > > redundant as already granted before > > exec sp_grantdbaccess N'<localcomputername>\<user>' > > exec sp_changegroup 'db_owner', '<localcomputername>\<user>' > >GO > and you should use > EXEC sp_addrolemember 'role', 'user'; > instead of sp_changegroup.. it's not that big issue as your user will only > be member of the public database role, and can not be eliminated from it... > but it's more correct :D > > another (very sad) way to accomplish a preliminary access (waiting for the > vendor to provide the path) could be to grant permissions (for each object) > to the public database role... each user is member of that role so this will > grant everyone the given permission(s) to all objects.. remember that the > "guest" database user (if not dropped already, I do it in the model > database, so that no guest is actually allowed :D) will be granted > permissions too.. > be very carefull :D > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz http://italy.mvps.org > DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools > --------- remove DMO to reply > >
hi Randall,
[quoted text, click to view] Randall Loftis wrote: > > One item I am concerned about is the install in the Admin role. The > install was performed under a Domain Administrator account and users > did not have access to the database nor could they start the > services. and they should not... starting/stopping/managing services does not compete them.. [quoted text, click to view] > Further as I recall I did have to perform the master db correct... after installation, only members of the local> sp_grantlogin or subsequent master db stored procedures would not > work. Builtin\Administrators role are granted connection to the instance... they are granted membership to the sysadmin server role as well, so they can perform whatever action/task they like on the specified instance.. but the "regular" path is to use such powerfull permissions only for particular task that requires those kind of privileges.. on the other side, interactive users are usually members of the WinNT (or domain) power users\users roles... and you can grant them connection as well at this role granularity via sp_grantlogin so that you do not have to manage each particular user (but you can)... soon after you are finished with the connection permissions, you have to deal with each database permissios as well.. only sysadmin members do not require to be granted db access.. so you have to grant all you interactive limited user db acces for each database they are interested with... finally, you have to deal with each specific object... only dbowner (and sysadmin server role members) can perform whatever task on the database.. so you manage CRUD (EXEC as well for stored procedures) permissions with user defined roles, or via the built in available database roles... always use the "least privilege principle" pattern... if a defined role/user does not require write permission on a specific object, do not grant it... :D do not "deny", but do only "grant" where and if necessary :D:D [quoted text, click to view] > When you refer to install as administrator do you refer to the yep... but an admin domain member is ok as well..> local administrator account? -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz http://italy.mvps.org DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools --------- remove DMO to reply
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |