GRANT CREATE DATABASE TO a domain user account or group in SQL 2005

Is it possible to GRANT CREATE DATABASE TO a domain user account or group?
I have both SQL Express 2005 & SQL 2005 Developer edition machines, both
have windows integrated security enabled.
I am trying to do something like:
GRANT CREATE DATABASE TO 'DomainName\DomainUserName'
I get an error that complains about the '\' being incorrect syntax.
However, if I do a
sp_addsrvrolemember 'DomainName\DomainUserName', 'dbcreator'
that works like a champ.
I have also tried this same test on a machine that is not part of the domain
(changing domain name to machine name of course).
Is this supported? How can I do it? Can I specify a domain group in it
instead?
Thanks for any information,
JoeTry:
GRANT CREATE DATABASE TO [DomainName\DomainUserName]
(Use square brackets instead of quotes.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Joe" <jwdaigle@.nospam.nospam> wrote in message
news:%236qm7LPpGHA.4116@.TK2MSFTNGP03.phx.gbl...
> Is it possible to GRANT CREATE DATABASE TO a domain user account or group?
> I have both SQL Express 2005 & SQL 2005 Developer edition machines, both
> have windows integrated security enabled.
> I am trying to do something like:
> GRANT CREATE DATABASE TO 'DomainName\DomainUserName'
> I get an error that complains about the '' being incorrect syntax.
> However, if I do a
> sp_addsrvrolemember 'DomainName\DomainUserName', 'dbcreator'
> that works like a champ.
>
> I have also tried this same test on a machine that is not part of the
> domain (changing domain name to machine name of course).
>
> Is this supported? How can I do it? Can I specify a domain group in it
> instead?
>
> Thanks for any information,
> Joe
>|||Hi Joe,
I agree with Arnie that you can use the square brackets to wrapper your
domain username and square brackets is mostly used to wrapper database
object names which contains particular characters. In addition, as for
GRANT/REVOKE statements, they can work on both domain users and domain
groups.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Joe,
Does the suggestion in the previous messages help you on this issue? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

grant (Alter table) permission on a single table in the database

Product: Microsoft SQL server Enterprise Edition (2000)
O/S: MS Windoes NT (5.2)
Version: 8.00.760
===================================================== Can we grant (Alter table) permission on a single table on a database?
( I need to create a new user in a database of about 100 tables. This User
needs modify(Change structure of table) permission on a single table. User
want to incorporate triggers into the table. Can someone suggest me the best
way to grant permission for this.
Thanks
--
ontario, canada
=====================================================db
If you make him to be owner of the table. Such as CREATE TABLE user.T(c
INT...)"db" <db@.discussions.microsoft.com> wrote in message
news:0C3AA563-11A3-42FA-989D-6330159005A3@.microsoft.com...
> Product: Microsoft SQL server Enterprise Edition (2000)
> O/S: MS Windoes NT (5.2)
> Version: 8.00.760
> =====================================================> Can we grant (Alter table) permission on a single table on a database?
> ( I need to create a new user in a database of about 100 tables. This User
> needs modify(Change structure of table) permission on a single table. User
> want to incorporate triggers into the table. Can someone suggest me the
> best
> way to grant permission for this.
> Thanks
> --
> ontario, canada
> =====================================================

Good Old Event ID: 18456 - Can't figure this one out!

Hi everyone,

I've got a clean SQL Server 2005 Enterprise Edition installation, with a domain account configured as the service account. My application log is now flooded with the following error message:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 5/25/2007
Time: 1:57:00 AM
User: TNG\TNG-SQL_Service
Computer: TNG-MOSSDB01
Description:
Login failed for user 'TNG\TNG-SQL_Service'. [CLIENT: <local machine>]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 0d 00 00 00 54 00 4e 00 ....T.N.
0010: 47 00 2d 00 4d 00 4f 00 G.-.M.O.
0018: 53 00 53 00 44 00 42 00 S.S.D.B.
0020: 30 00 31 00 00 00 07 00 0.1.....
0028: 00 00 6d 00 61 00 73 00 ..m.a.s.
0030: 74 00 65 00 72 00 00 00 t.e.r...

And when I open up the SQL logs, I'm getting two errors - one is essentially the same as the above, and the other is:

Date 5/25/2007 1:51:00 AM
Log SQL Server (Current - 5/25/2007 1:51:00 AM)

Source Logon

Message
Error: 18456, Severity: 14, State: 16.

Now I've seen all kinds of posts about granting rights to master and such - and I have, but these errors are not going away. I've gone so far as to create new service accounts and even switch over to the local system account - and the error still occurs. Does anyone have any insight into other routes / approaches I can take with this?

Thank you,

Chris

From BOL topic Troubleshooting: Login failed for user 'x'

Additional Error Information

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

State Description

2

User ID is not valid.

5

User ID is not valid.

6

An attempt was made to use a Windows login name with SQL Server Authentication.

7

Login is disabled, and the password is incorrect.

8

The password is incorrect.

9

Password is not valid.

11

Login is valid, but server access failed.

12

Login is valid login, but server access failed.

18

Password must be changed.

Other error states exist and signify an unexpected internal processing error.

You may need to contact Support for this.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Hi Steve,

Do you by chance have any information on State = 16?

Thanks,
Chris

|||

It is related to not being able to establish a database session.

Check the default_database for your account and make sure it is master.

How are you changing the service account? Please make sure you use the tools provided in sql server because usinging windows scm will not do the whole job.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steve,

The default_database for the account is master, and the service accounts were set through the SQL Server Configuration Manager. I went back into it and reset all of them just in case - but still getting the error. Just doesn't make any sense. In fact, I'm also seeing the same error for another account - the service account I use with SharePoint (residing on another server). Same exact errors in the Event Log for the Server & the SQL Logs.

Are there any statements I can run against the master to see if maybe an entry is missing? I'm drawing at straws with this.

Thanks,
Chris

|||

You can try the following statements to see if there is anything unexpected:

SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name

FROM sys.server_principals

WHERE name = 'login_name'

go

SELECT name, database_id, user_access_desc, state_desc, is_in_standby

FROM sys.databases

WHERE name = 'default_db_name'

go

SELECT suser_name(role_principal_id), suser_name(member_principal_id)

FROM sys.server_role_members

WHERE member_principal_id = suser_id( 'login_name' )

go

SELECT class_desc, suser_name(grantee_principal_id), permission_name, state_desc

FROM sys.server_permissions

WHERE grantee_principal_id = suser_id( 'login_name' )

Go

Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission.

I hope this helps,

-Raul Garcia

SDE/T

SQL Server Engine

Goldmine Premium Edition

I am considering purchasing the upgrade for Goldmine Premium Edition but I was told I must download SQL Express. How difficult is this to download and will I have problems with other programs? Is this download on auto download from Microsoft. If so, how do I know if it is already on my system?

hi,

SQLExpress download is way easy... just go to http://msdn.microsoft.com/vstudio/express/sql/download/ and download and install the required/desired bits.. eventually have a look at http://msdn2.microsoft.com/en-us/library/ms143441.aspx..

regards

Going to SQL7 DE to SQL 2000 MSDE

Hi All
Does anyone know if there is a path to change a database
from MSSQL 7 Desktop Edition (note, not desktop engine) to
MSDE 2000 ?
Thanks
JI very much doubt that there's some setup program that will upgrade the 7.0
DE to 2000 MSDE. My guess is that you have to use backup/restore or
detach/attach - something that you can't do for the system databases (they
can't cross editions).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
> Hi All
> Does anyone know if there is a path to change a database
> from MSSQL 7 Desktop Edition (note, not desktop engine) to
> MSDE 2000 ?
> Thanks
> J|||Hi
Do you recommend that I first update the database to SQL
2000 and then do a backup and restore into MSDE?
Do you know where I can find documentation on restoring
into MSDE?
Thanks
J
>--Original Message--
>I very much doubt that there's some setup program that
will upgrade the 7.0
>DE to 2000 MSDE. My guess is that you have to use
backup/restore or
>detach/attach - something that you can't do for the
system databases (they
>can't cross editions).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"J" <anonymous@.discussions.microsoft.com> wrote in message
>news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
>> Hi All
>> Does anyone know if there is a path to change a database
>> from MSSQL 7 Desktop Edition (note, not desktop engine)
to
>> MSDE 2000 ?
>> Thanks
>> J
>
>.
>|||I'm not sure. I haven't dealt with MSDE so I don't know about all the setup
quirks that MSDE has. But upping to Personal Edition 2000 first sounds like
a good idea (make it the same service pack as well), because you now have
the option to restore both system and user databases as well. The restore
process is the same for a regular SQL Server, the RESTORE command. I suggest
you post to the msde group, as you might find more specific suggestions
there.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:1147a01c3bf5f$63eb4ff0$a601280a@.phx.gbl...
> Hi
> Do you recommend that I first update the database to SQL
> 2000 and then do a backup and restore into MSDE?
> Do you know where I can find documentation on restoring
> into MSDE?
> Thanks
> J
>
> >--Original Message--
> >I very much doubt that there's some setup program that
> will upgrade the 7.0
> >DE to 2000 MSDE. My guess is that you have to use
> backup/restore or
> >detach/attach - something that you can't do for the
> system databases (they
> >can't cross editions).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"J" <anonymous@.discussions.microsoft.com> wrote in message
> >news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
> >> Hi All
> >> Does anyone know if there is a path to change a database
> >> from MSSQL 7 Desktop Edition (note, not desktop engine)
> to
> >> MSDE 2000 ?
> >> Thanks
> >> J
> >
> >
> >.
> >

Going from Trial to Developer edition

My trial is about to end and I have purchased the developer edition disks

When the trial expires is there the opportunity to just enter the product key
or do I need to install. If so, the trial programs are under my C:\Program Files\Microsoft SQL Server folder. Can I just use another folder for the developer software or will I need to do some backup and overwrite
Thanks for any help

You can do either

1. Install SQL DEV side by side with Trial version, which is supported.

2. Transfer all data in Trial version to SQL DEV.

3. Uninstall Trial version when SQL DEV's behavior is stable.

Or

Upgrade Trial version to SQL DEV directly. Make sure you select to upgrade under UI and choose the same instance name.

Before you perform any major operation, please back up your database.

|||Thanks. In the end i backed up, upgraded over and then reattached data
Seems to be working fine