Tokatrash wrote:
> Hi all,
>
> I have a MSDE database that i can fully use as long as the user has
> administrator privileges.
> But due to security i cannot allow that.
>
> Does any of you know how to give a regular full privileges to a
> database using Windows authentication?
>
> I have looked through a lot of mailing lists, and a lot of MS articles
> but none of them have the answer to my problem.
>
> Thanks a lot!

Andrea Montanari wrote:
> I do think I did not understand your question...
> MSDE installs by default a WinNT group, named Builtin\Administrators, and
> all local admin members will be automatically included in this login
> group...
> users and power users will not, as the related WinNT group is not created by
> default... but you can add them as required... from then on, every power
> user and limited user WinNT member will be granted login to the SQL Server
> instance with the privileges you set for them... if you granted (ie) power
> users login membership to dbcreator server role, each power user member will
> be able to create new databases... if, at database level, you granted them
> db_backupoperator database role membership, they will be able to backup the
> related database...
> --
> 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

Andrea Montanari wrote:
> for each "user" you want to log on SQL Server, you have to manage his/her
> WinNT login... or, with a higher granularity, you can manage the WinNT group
> he/she is member of... and this is the 1st phase of security management in
> SQL Server, which is managed via login's related procedures (in SQL Server
> 2005 a new DML syntax has provided via CREATE LOGIN ... instead of the
> legacy sp_addlogin and sp_grantlogin)... the WinNT login just has been
> granted login permision to the SQL Server instance...
>
> the second authentication phase is at database level, where each login will
> be granted database access mapping to a database user... registered users
> can access the specified database as they will be by default member of the
> "public" database role... but usually "public members" will not be
> authorized al lot of permissions on database objects..
> the mapping is performed in the JOIN database..sysusers.sid =
> master..syslogins.sid , so the only link is the provided Login's sid, it's
> Security IDentification number so, the second phase regards a database
> security implementation... in order to access a specified database the
> simple login existance does not provide database access, but a (database)
> user must be mapped to the corresponding login.. and is about verifying that
> at each object level (including database, tables, views, columns, procedures
> and so on) the Login/User association is permitted access to...
> so the objects access permissions comes to play... where you define them
> granting users/roles SELECT/DELETE/EXECUTE (and so on) privileges at an
> object level (or column level for tables and views)..
>
> logins member of the sysadmin server role do not require all this, as
> sysadministrators can perform whatever task/action on a SQL Server instance,
> but "normal users" should be mapped that way... obviously you should not
> resort on sysadmin membership for "traditional" database activities like
> querying the database :D
> --
> 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

Andrea Montanari wrote:
> I beg your pardon :)
> for your convenience, as MSDE does not provide a user interface
> administration tool like Enterprise Manager, you can have a look at a free
> prj of mine, DbaMgr2k, at the link following my sign., which provieds one
> similar to Enterprise Manager...
> a "regular" WinNT user is not usually granted access to SQL Server...
> you have to provide your self.. that's to say grant him (or his WinNT group)
> access to SQL Server...
> to do that, navigate to the "Logins" node, and add a new login... says it
> will be "userA"...
> type Domain\userA, and select the Windows NT Authentication
> now, YourDomain\userA (but coul'd even be ComputerName\userA) has been
> granted login to the SQL Server instance..
> to grant him access to a specified database, select the login's
> "properties", and access the "Database access" tab...
> in the upper grid, select the database the user should be able to access...
> say it's "Pubs" database (a sample database that comes with SQL Server)
> in the lower grid, there's a list of builtin database roles...
> usually normal users should not be granted access to data directly (tables),
> but only to stored procedures and/or views... but we'll say taht you need
> direct access to tables...
> select and set the db_datareader and db_datawriter properties...
> now, login YourDomain\userA will be mapped to a database user, (userA) that
> will be member of the 2 named database roles...
> inheriting those roles permissions, he will be able to read from and write
> to all user tables in that database..
> --
> 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

Tokatrash wrote:
>
> I use the database on a Windows 2003 SBS server.
> So i believe that the group Builtin\Administrators was allready there.
> But how do i grant a regular user or a power user the privileges to
> use the databases?
> As soon as i add a user to the Builtin\Administrators it all works
> fine, but then they have privileges to control the whole server, and
> that's not an option.
>

Tokatrash wrote:
> Andrea i'm sorry if don't understand you right, but what i am looking
> for is a way to give a regular user on a Windows 2003 Small Business
> Server access to a MSDE database.
>
> Whenever i try to access the database with a regular user i get
> permission denied. Only with Administrator privileges i can connect to
> the database.
>
> Where can i set these privileges?
> I'm running the MSDE with regular Windows Authentication.
>
> I hope i'm explaining it correctly, i simply just don't understand
> you're last answer.
> Sorry, and thanks for all you're help.