Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!
"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegr oups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David
|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?
sql

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

grant exec to windows account

When i do
grant exec on sproc1 to 'domain\user'
it returns
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'domain\user'Hi John
Use [ ] instead of quotes:
grant exec on sproc1 to [domain\user]
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"John Doe" <Johndoe@.jd.com> wrote in message
news:Oj4ImmmbIHA.1212@.TK2MSFTNGP05.phx.gbl...
> When i do
> grant exec on sproc1 to 'domain\user'
> it returns
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'domain\user'
>

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 trace to a domain account

surprisingly, couldn't find anything about granting alter trace permissions to a domain account.

I try to run following sql:

GRANT ALTER TRACE TO MyDomain\User1;
GO

And get the following error when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

What is correct SQL to grant alter trace permissions to a domain account?

TIA.

You must enclose the name between brackets:

GRANT ALTER TRACE TO [MyDomain\User1];

Thanks
Laurentiu

|||Thanks much for your help. Much appreciated.sql

grant alter trace to a domain account

surprisingly, couldn't find anything about granting alter trace permissions to a domain account.

I try to run following sql:

GRANT ALTERTRACE TO MyDomain\User1;
GO

And get the following error when executing:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

What is correct SQL to grant alter trace permissions to a domain account?

TIA.

You must enclose the name between brackets:

GRANT ALTER TRACE TO [MyDomain\User1];

Thanks
Laurentiu

|||Thanks much for your help. Much appreciated.

GPInventory with XML Output / import it into SQL

Hello all,

I am trying to use a free download from Microsoft called GP Inventory

to collect minimal data about the computers within my Domain.

This of course is easy enough to do and saves rather nicely into an XML

document.

I'd like to be able to work with this information in a more managable

format (for me anyway) so I was hoping to import it into a SQL DB.

Anyway, so I went to MS Knowledge base and came up with the following tutorial on the BulkLoad method.

http://support.microsoft.com/kb/316005/en-us

Which I was able to duplicate using copy and paste. In looking at

the different parts of this process I felt as though doing the same

thing for my GPInventory output would be easy enough to do. (Evidently

Not).

Is there anyway someone can help an XML rookie figure out how to do this?

Here is a piece of my data, my scheme, and my script. As of right

now the script is throwing an error at me which says: Schema: Missing

Element Definition "ManagedObject"

XML

<ComputerList>

<Computer Name="ACCT-520" QueryState="Queried" Username="">

<ManagedObject Name="WMI: Operating System" Query="Select Caption from Win32_OperatingSystem">

<Attribute Name="Caption" Value="Microsoft Windows 2000 Professional" />

</ManagedObject>

</Computer>

<Computer Name="ACCT-627" QueryState="Queried" Username="">

<ManagedObject Name="WMI: Operating System" Query="Select Caption from Win32_OperatingSystem">

<Attribute Name="Caption" Value="Microsoft Windows XP Professional" />

</ManagedObject>

</Computer>

</ComputerList>

Schema

<?xml version="1.0" ?>

<Schema xmlns="urn:schemas-microsoft-com:xml-data"

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="Computer" dt:type="string" />

<ElementType name="Attribute" dt:type="string" />

<ElementType name="Computerlist" sql:is-constant="1">

<element type="ManagedObject" />

</ElementType>

<ElementType name="Customers" sql:relation="Customer">

<element type="Computer" sql:field="Computer" />

<element type="Attribute" sql:field="Attribute" />

</ElementType>

</Schema>

Script

set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")

objBL.ConnectionString = "provider=SQLOLEDB.1;data source=Intranet;database=MyDatabase;uid=Asset;pwd=fakepass"

objBL.ErrorLogFile = "c:\error.log"

objBL.Execute "c:\map4.xml", "c:\customers3.xml"

set objBL=Nothing

You need to map your xml into a relational database using sql:relation and sql:relationship annotations. If you haven't already designed a relational database, bulkload can generate one from your annotations.

Here is a good introductory:

http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/exchsqlxml.asp

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

Global "User!UserID" as Parameter-Default

Hi all, I use the Global "User!UserID" as a Default-Value for a parameter!
But if a user starts the report, he alsway sees my ID (Domain\MyID) instead
of the User's ID (Domain\UserID). Is that a Bug or do I make something wrong?
Thanks, RolandAnother way of passing your UserID into your query is not to pass it as a
"Report Parameter" instead in Designer, go to your data tab, select the data
set and click on the elipses(...) to edit. Click on the Parameters tab and
enter your User!UserID. Make sure you take out the parameterID from the other
area(Reports Parameter). Hope this makes sense.
"Roland Müller" wrote:
> Hi all, I use the Global "User!UserID" as a Default-Value for a parameter!
> But if a user starts the report, he alsway sees my ID (Domain\MyID) instead
> of the User's ID (Domain\UserID). Is that a Bug or do I make something wrong?
> Thanks, Roland