Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Thursday, March 29, 2012

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names
"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David
|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:

>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>

Granting Select to an NT Login Group

I have this procdure to grant select permission to a developers group on an
SQL Databse. The query executes but never returnes and the permissions are
set. Can anyone tell me why the little world keeps spinning and never
returns. thanks
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE (@.@.FETCH_STATUS <> 1)
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
DEALLOCATE table_names
CLOSE table_names"brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
>I have this procdure to grant select permission to a developers group on an
> SQL Databse. The query executes but never returnes and the permissions
> are
> set. Can anyone tell me why the little world keeps spinning and never
> returns. thanks
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE (@.@.FETCH_STATUS <> 1)
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> DEALLOCATE table_names
> CLOSE table_names
Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
Should beL
DECLARE @.cmd as varchar(255)
DECLARE table_names CURSOR FOR
SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
DECLARE @.name as varchar(255)
OPEN table_names
FETCH NEXT FROM table_names INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
--PRINT @.cmd
EXEC @.cmd
FETCH NEXT FROM table_names INTO @.name
END
CLOSE table_names
DEALLOCATE table_names
David|||Thanks David
"David Browne" wrote:
>
> "brymer28303" <brymer28303@.discussions.microsoft.com> wrote in message
> news:D3BA7C42-13A0-49EF-A476-3A32121E2D41@.microsoft.com...
> >I have this procdure to grant select permission to a developers group on an
> > SQL Databse. The query executes but never returnes and the permissions
> > are
> > set. Can anyone tell me why the little world keeps spinning and never
> > returns. thanks
> >
> > DECLARE @.cmd as varchar(255)
> >
> > DECLARE table_names CURSOR FOR
> > SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> > DECLARE @.name as varchar(255)
> >
> > OPEN table_names
> > FETCH NEXT FROM table_names INTO @.name
> > WHILE (@.@.FETCH_STATUS <> 1)
> > SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> > --PRINT @.cmd
> > EXEC @.cmd
> > FETCH NEXT FROM table_names INTO @.name
> > DEALLOCATE table_names
> > CLOSE table_names
> Infinite loop. Your loop doesn't include the FETCH NEXT. Also you should
> test for @.@.FETCH_STATUS = 0, since values other that 1 are possible.
> Should beL
> DECLARE @.cmd as varchar(255)
> DECLARE table_names CURSOR FOR
> SELECT [name] FROM sysobjects WHERE type = 'u' ORDER BY [name]
> DECLARE @.name as varchar(255)
> OPEN table_names
> FETCH NEXT FROM table_names INTO @.name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT @.cmd = 'GRANT SELECT ON ' + @.name + ' TO [VS Developers]'
> --PRINT @.cmd
> EXEC @.cmd
> FETCH NEXT FROM table_names INTO @.name
> END
> CLOSE table_names
> DEALLOCATE table_names
>
> David
>sql

Granting SA rights to Sr. Developers in Small ITS Groups when ther

I just finished reading several articles on the SQL Server Security Checklis
t
or Best Practices - and I could use some advise. Our ITS group is too small
to have a full time [application] DBA for SQL Servers so only Networking has
SA rights on the servers which in the past was not a problem. But now that
we're starting to develop/implement new Coldfusion and SQL Server Database
applications as we head toward switching from static HTML to a dynamic
database driven multiple server production environment that will use both a
staging and a development server. Since these are new technologies to this
environment and the production servers that only contain public information
(i.e. do not contain high risk data), Development has requested read-only
access to the production and staging servers to look at the data integrity,
check the rev. number on the code, and look at IIS, CF, and SQL logs and
configuration files, as needed. Development has also requested SA rights fo
r
their Sr. Developers only on the development server (i.e not the product
servers) to be used for testing replication, running diagnostics, reviewing
logs, create test applications users/roles to test permissions, test
configuration settings and permission issues (we are also implementing activ
e
directory) but access was denied to both requests. With one month left
before going live with the new production servers on IIS/CF/SQL, development
is concerned with the time delays with testing or replicating problems on th
e
development server and the inability to diagnose production problems with
outdated logs (vs real time monitors of IIS/CF).
The networking staff are competent at network issue but are not DBAs and doe
not have knowledge of how setting and permissions in SQL Server (not to
mention IIS or CF) will affect applications. How should SQL Server
roles/permission be split between Networking and Development on the
Development server in our small environment (note: our Sr Developers have 5
-
25 years of development experience including commercial DB engineering)?hi
there are no hard and fast rules to set a role to the users of the database.
it depends on the policy that u draft.
U need not have a full time DBA, eventhough its advised to have. Senior
developers with good admin skills can take up the role.
the person should have knowledge on taking backups and crash recovery
hope this answers the question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"JA" wrote:

> I just finished reading several articles on the SQL Server Security Checkl
ist
> or Best Practices - and I could use some advise. Our ITS group is too sma
ll
> to have a full time [application] DBA for SQL Servers so only Networking has
> SA rights on the servers which in the past was not a problem. But now tha
t
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to th
is
> environment and the production servers that only contain public informatio
n
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data integrity
,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics, reviewin
g
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing act
ive
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL, developme
nt
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and d
oe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||It sounds like you're between a political rock and a hard place. The
important thing is to save in your CYA file a copy of the memo informing
management that what's going live cannot be adequately tested without
access, and since access has been denied, that they'll have to live with the
possibility of a meltdown. Once a meltdown occurs, you can be absolutely
sure that you will be given all the access you need (and probably more) in
order to fix the problem. (You might want to plan on an all-night debugging
fest, so be sure to stock up on Mountain Dew.)
Another solution would be to request equipment identical to the production
environment that is dedicated and accessible to the ITS group just for
testing. Of course someone will have to pay for the new equipment, and
you'll probably have to delay going live until the new equipment is
configured and testing is completed.
"JA" <JA@.discussions.microsoft.com> wrote in message
news:9E08BED2-6BEA-46B7-966E-64A6ADE1AD19@.microsoft.com...
> I just finished reading several articles on the SQL Server Security
Checklist
> or Best Practices - and I could use some advise. Our ITS group is too
small
> to have a full time [application] DBA for SQL Servers so only Networking
has
> SA rights on the servers which in the past was not a problem. But now
that
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to
this
> environment and the production servers that only contain public
information
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data
integrity,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics,
reviewing
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing
active
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL,
development
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and
doe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||SA rights on a development box does not sound unreasonable; in fact, I
recommend it, for many of the reasons you list above. Developers need
their own space to break stuff, so that they understand how NOT to
break it in production.
I work in a small shop as well, and currently I serve as both
production and development DBA; we're hiring a production DBA, but
until then I have to document every little thing I do to make sure that
there is a record for accountability depending on the hat I'm wearing.
I think it's OK for developers to have read-only access to
llimited-risk data in production, and they should have full control
over their development environment. And you can tell your bosses I
said so :)
Someone on the Interwebs believes in your cause.
Stu|||On Fri, 29 Jul 2005 22:56:02 -0700, JA wrote:
(snip)
>The networking staff are competent at network issue but are not DBAs and do
e
>not have knowledge of how setting and permissions in SQL Server (not to
>mention IIS or CF) will affect applications. How should SQL Server
>roles/permission be split between Networking and Development on the
>Development server in our small environment (note: our Sr Developers have 5
-
>25 years of development experience including commercial DB engineering)?
Hi JA,
I tend to agree with Stu: SA rights on the dev. server for experienced
DB developers and read rights to non-sensitive stuff on the prod server
sounds reasonable.
On the other hand, the networking staff has been assigned the
responsibility to keep the databases up and running and to protect data
from from unauthorized spreading (I'm sure that this is a terrible
sentence, but I don't know how to put it in correct English - I hope you
do understand what I'm trying to say).
And since it's THEIR responsibility, they are free to take whatever
measures THEY think are needed to accomplish that goals.
Many years ago, I had to do some major reshuffling on a SQL Server
implementation. The sa access I had was enough for about half the things
I needed to do (like shutting down the service, or creating a new file),
but many other tasks (like restarting the service, and deleting files no
longer needed) were only available for the network admins. Of course, I
requested access as network admin, to streamline the process. And of
course, I didn't get it. So I phoned the network admins each time I
needed them to do something:
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you delete file XXX on server YYY please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - file AAA has to be moved from drive BBB to CCC."
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - I requested a restart of the SQL Server service some 5
minutes ago, but doesn't seem to be running"
And so on.
Guess how long it took before I DID get some extra rights?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When I worked help desk, we used to hate guys like you :)
Stu|||On 30 Jul 2005 16:38:27 -0700, Stu wrote:

>When I worked help desk, we used to hate guys like you :)
Hey! I was only trying to get my job done, you know!
(mumble, whine, mumble)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting Permissions using SQL 2005 Schema...

All,

I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema.

I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users].

I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema.

Anyone have any ideas?

Thanks,

Justin

They would need alter schema to drop tables in the schema.

GRANT ALTER ON SCHEMA::[Sandbox] TO [Sandbox Users]

-Sue

|||

CONTROL should cover ALTER and DELETE. My guess is that the users from that group have been denied some permission that is affecting their DELET statements.

You can make use of fn_my_permissions and has_perm_by_name to find out the actual permissions on the object, for example:

-- Connected as/impersonating a member of Sandbox users

--

SELECT * FROM fn_my_permissions( 'Sandbox', 'schema' )

go

SELECT has_perms_by_name( 'Sandbox.SampleTable', 'object', 'DELETE' )

go

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for catching that Raul...not even sure what I was thinking last night. Or not thinking at that moment.

-Sue

|||

Raul,

Thanks... that will help. I will be working with the user this morning to see if I can figure out why he is having this problem.

It looks like the permissions are fine. I was helping him out this morning and I think he has an issue with the package he was trying to run, bu the fn_my_permissions helped out tremendously.

Thanks!

Justin

Granting Permissions on Multiple Tables

I created a new Role called GRPSELECT. I want to give this group the abilit
y to only run the SELECT statement on all the tables in my database. Right
now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it
only allows me to do this
to one table at a time.
How can I accomplish this to all 600 tables in the database without manually
typing in every single table?
Thanks in advance!Instead of creating a new role you can add these users/group to
db_datareader fixed db role.
Members of db_datareader fixed db role have select permissions on any
objects in the db.
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclaimer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Jon Jones" <Jon Jones@.discussions.microsoft.com> wrote in message
news:96F7208A-EA64-467A-8AD2-5477782ED32E@.microsoft.com...
> I created a new Role called GRPSELECT. I want to give this group the
ability to only run the SELECT statement on all the tables in my database.
Right now I can run "GRANT SELECT ON table TO GRPSELECT" in Query Analyzer,
but it only allows me to do this to one table at a time.
> How can I accomplish this to all 600 tables in the database without
manually typing in every single table?
> Thanks in advance!|||Jon,
You can use Transact-SQL to generate the script for you:
Ex:
SELECT 'GRANT SELECT ON ' + so.name + ' TO GRPSELECT'
FROM dbo.sysobjects so
WHERE so.type = 'u'
This output can then be copied.
Randy Dyess
"Jon Jones" wrote:

> I created a new Role called GRPSELECT. I want to give this group the ability to o
nly run the SELECT statement on all the tables in my database. Right now I can run
"GRANT SELECT ON table TO GRPSELECT" in Query Analyzer, but it only allows me to do
thi
s to one table at a time.
> How can I accomplish this to all 600 tables in the database without manual
ly typing in every single table?
> Thanks in advance!sql

Tuesday, March 27, 2012

Granting a user permissions to create and Drop a table

How do I allow a user (or group of users) permission to create/drop a table?

I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.

thanksWhat I want to know as well is, if the table is DROPPED, do I have to re-set all permissions on it again after it is re-created?
If so, what is the best way to achieve all this/

Thanks|||

See permission section of the DROP TABLE article in BOL. Permissions would have to be reassigned - they are dropped when the table is dropped. You should script all your database operations and manage script files with a source code control product.

Thanks
Laurentiu

Monday, March 26, 2012

Grant login to remotemachine\ASPNET

Hello Group,
I am working on an ASP.Net project. It will work on the same server that
runs SQL Server. For that reason all I did was grant the server's ASPNet
account access to the database. The problem is that when I am debugging
(from my remote machine) I can't connect because my station's ASPNet account
is not recognized by the server, as it only accept its own server\ASPNet
account.
How can I grant login to my remotemachine\ASPNet Account to that SQL Server?
Thanks ahead for all replies!
Giovanni BassiHi,
For debugging,USE domain user (Not remotemachine\ASPNet)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"Giovanni Bassi" <gbassi@.coair.com> wrote in message
news:e66XMvNlDHA.1708@.TK2MSFTNGP12.phx.gbl...
> Hello Group,
> I am working on an ASP.Net project. It will work on the same server that
> runs SQL Server. For that reason all I did was grant the server's ASPNet
> account access to the database. The problem is that when I am debugging
> (from my remote machine) I can't connect because my station's ASPNet
account
> is not recognized by the server, as it only accept its own server\ASPNet
> account.
> How can I grant login to my remotemachine\ASPNet Account to that SQL
Server?
> Thanks ahead for all replies!
> Giovanni Bassi
>sql

Friday, March 23, 2012

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 command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >

Wednesday, March 21, 2012

Grabbing the Top Nth Records based on another table per group

Hi All,

I am trying to perform a SQl query that I have never attempted before.

I have a table with records such that would look like this

GroupField1 GroupField2 Field1 Amount

AAA BBB Field1Data 23.00

AAA BBB Feld2Data 24.00

AAA BBB Field3Data 8.00

BBB CCC Field1Data 25.00

BBB CCC Field2 Data 23.00

DDD BBH Field1Data 232.00

I have a second table that is like

GroupField1 GroupField2 NumberOfRows

AAA BBB 2

BBB CCC 1

DDD BBH 1

What I am trying to do is create a query that gets data from table 1 based on how many rows table 2 says to get.

I think the query would be something like

Select Top(select number of rows from table 2 where Table1.GroupField1 = Table2.GroupField1 and Table1.GroupField2 = Table2.GroupField2) from Table1

Anybody oput there have any expertise in this area?

Thank you,

Lynn

Wrong forum. Try posting on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Monday, March 19, 2012

Got my SQLIO Results... Now what?

I posted a similar message to SQL Tools group, apologize for the
double-post, but this is urgent...
I ran SQLIO against my SAN and got back some encouraging results. It
basically seemns to point to optimal performance settings of 64KB I/O's,
4096KB "stripes", with the Buffering option to "All". How can I make sure
Windows, SQL, et al. are using the same options that SQLIO used? Is there
something that needs to be set in the registry to ensure 64KB I/O's/4096KB
"stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do I
need to modify settings in SQL (2000), or what?
Thanks
In SQL 2000 you do not have to set any knobs.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>I posted a similar message to SQL Tools group, apologize for the
>double-post, but this is urgent...
> I ran SQLIO against my SAN and got back some encouraging results. It
> basically seemns to point to optimal performance settings of 64KB I/O's,
> 4096KB "stripes", with the Buffering option to "All". How can I make sure
> Windows, SQL, et al. are using the same options that SQLIO used? Is there
> something that needs to be set in the registry to ensure 64KB I/O's/4096KB
> "stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do
> I need to modify settings in SQL (2000), or what?
> Thanks
>
|||That's good to know. Now I need to know I *can* do with the SQLIO results.
So far the only use I've found is to print out hard copies so I'll always
have something handy to wrap raw fish with.
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> In SQL 2000 you do not have to set any knobs.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>
|||SQLIO results can be useful if you experience IO bottleneck in SQL Server.
You should collect windows and SQL server performance counters as well if
you suspect such a problem.
The primary solution to IO problems are: 1)configure files and filegroups in
your database so that you can use your existing disk volumes better; 2) get
better hardware. SQL Server automatically issues the optimal IO size depends
on your file/filegroup configuration.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:E3BZd.4121$So4.369@.fe10.lga...
> That's good to know. Now I need to know I *can* do with the SQLIO
results.[vbcol=seagreen]
> So far the only use I've found is to print out hard copies so I'll always
> have something handy to wrap raw fish with.
> "wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
> news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
I/O's,[vbcol=seagreen]
sure[vbcol=seagreen]
(2003
>
|||Thanks for the feedback Wei. Unfortunately we've got top of the line
hardware and have configured the heck out of files and filegroups in the
database. In fact, SQL Server isn't the issue at all. We eliminated SQL
Server completely as a bottleneck. But my understanding was that SQLIO
measures the speed of the Disk I/O subsystem, not SQL Server, anyway.
Perhaps I read the documentation wrong. Anyways, I've decided that using
SQLIO is a dead end, so I'm on to other tools. Thanks.
"Wei Xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:423cb5b4$1@.news.microsoft.com...
> SQLIO results can be useful if you experience IO bottleneck in SQL Server.
> You should collect windows and SQL server performance counters as well if
> you suspect such a problem.
> The primary solution to IO problems are: 1)configure files and filegroups
> in
> your database so that you can use your existing disk volumes better; 2)
> get
> better hardware. SQL Server automatically issues the optimal IO size
> depends
> on your file/filegroup configuration.
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:E3BZd.4121$So4.369@.fe10.lga...
> results.
> I/O's,
> sure
> (2003
>

Got my SQLIO Results... Now what?

I posted a similar message to SQL Tools group, apologize for the
double-post, but this is urgent...
I ran SQLIO against my SAN and got back some encouraging results. It
basically seemns to point to optimal performance settings of 64KB I/O's,
4096KB "stripes", with the Buffering option to "All". How can I make sure
Windows, SQL, et al. are using the same options that SQLIO used? Is there
something that needs to be set in the registry to ensure 64KB I/O's/4096KB
"stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do I
need to modify settings in SQL (2000), or what?
ThanksIn SQL 2000 you do not have to set any knobs.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>I posted a similar message to SQL Tools group, apologize for the
>double-post, but this is urgent...
> I ran SQLIO against my SAN and got back some encouraging results. It
> basically seemns to point to optimal performance settings of 64KB I/O's,
> 4096KB "stripes", with the Buffering option to "All". How can I make sure
> Windows, SQL, et al. are using the same options that SQLIO used? Is there
> something that needs to be set in the registry to ensure 64KB I/O's/4096KB
> "stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do
> I need to modify settings in SQL (2000), or what?
> Thanks
>|||That's good to know. Now I need to know I *can* do with the SQLIO results.
So far the only use I've found is to print out hard copies so I'll always
have something handy to wrap raw fish with.
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> In SQL 2000 you do not have to set any knobs.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>>I posted a similar message to SQL Tools group, apologize for the
>>double-post, but this is urgent...
>> I ran SQLIO against my SAN and got back some encouraging results. It
>> basically seemns to point to optimal performance settings of 64KB I/O's,
>> 4096KB "stripes", with the Buffering option to "All". How can I make sure
>> Windows, SQL, et al. are using the same options that SQLIO used? Is
>> there something that needs to be set in the registry to ensure 64KB
>> I/O's/4096KB "stripes"/Buffering=All, or somewhere else in Windows (2003
>> Server), or do I need to modify settings in SQL (2000), or what?
>> Thanks
>|||SQLIO results can be useful if you experience IO bottleneck in SQL Server.
You should collect windows and SQL server performance counters as well if
you suspect such a problem.
The primary solution to IO problems are: 1)configure files and filegroups in
your database so that you can use your existing disk volumes better; 2) get
better hardware. SQL Server automatically issues the optimal IO size depends
on your file/filegroup configuration.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:E3BZd.4121$So4.369@.fe10.lga...
> That's good to know. Now I need to know I *can* do with the SQLIO
results.
> So far the only use I've found is to print out hard copies so I'll always
> have something handy to wrap raw fish with.
> "wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
> news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> > In SQL 2000 you do not have to set any knobs.
> >
> > --
> > Wei Xiao [MSFT]
> > SQL Server Storage Engine Development
> > http://blogs.msdn.com/weix
> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Michael C#" <xyz@.yomomma.com> wrote in message
> > news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
> >>I posted a similar message to SQL Tools group, apologize for the
> >>double-post, but this is urgent...
> >>
> >> I ran SQLIO against my SAN and got back some encouraging results. It
> >> basically seemns to point to optimal performance settings of 64KB
I/O's,
> >> 4096KB "stripes", with the Buffering option to "All". How can I make
sure
> >> Windows, SQL, et al. are using the same options that SQLIO used? Is
> >> there something that needs to be set in the registry to ensure 64KB
> >> I/O's/4096KB "stripes"/Buffering=All, or somewhere else in Windows
(2003
> >> Server), or do I need to modify settings in SQL (2000), or what?
> >>
> >> Thanks
> >>
> >
> >
>|||Thanks for the feedback Wei. Unfortunately we've got top of the line
hardware and have configured the heck out of files and filegroups in the
database. In fact, SQL Server isn't the issue at all. We eliminated SQL
Server completely as a bottleneck. But my understanding was that SQLIO
measures the speed of the Disk I/O subsystem, not SQL Server, anyway.
Perhaps I read the documentation wrong. Anyways, I've decided that using
SQLIO is a dead end, so I'm on to other tools. Thanks.
"Wei Xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:423cb5b4$1@.news.microsoft.com...
> SQLIO results can be useful if you experience IO bottleneck in SQL Server.
> You should collect windows and SQL server performance counters as well if
> you suspect such a problem.
> The primary solution to IO problems are: 1)configure files and filegroups
> in
> your database so that you can use your existing disk volumes better; 2)
> get
> better hardware. SQL Server automatically issues the optimal IO size
> depends
> on your file/filegroup configuration.
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:E3BZd.4121$So4.369@.fe10.lga...
>> That's good to know. Now I need to know I *can* do with the SQLIO
> results.
>> So far the only use I've found is to print out hard copies so I'll always
>> have something handy to wrap raw fish with.
>> "wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
>> news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
>> > In SQL 2000 you do not have to set any knobs.
>> >
>> > --
>> > Wei Xiao [MSFT]
>> > SQL Server Storage Engine Development
>> > http://blogs.msdn.com/weix
>> >
>> >
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> > "Michael C#" <xyz@.yomomma.com> wrote in message
>> > news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>> >>I posted a similar message to SQL Tools group, apologize for the
>> >>double-post, but this is urgent...
>> >>
>> >> I ran SQLIO against my SAN and got back some encouraging results. It
>> >> basically seemns to point to optimal performance settings of 64KB
> I/O's,
>> >> 4096KB "stripes", with the Buffering option to "All". How can I make
> sure
>> >> Windows, SQL, et al. are using the same options that SQLIO used? Is
>> >> there something that needs to be set in the registry to ensure 64KB
>> >> I/O's/4096KB "stripes"/Buffering=All, or somewhere else in Windows
> (2003
>> >> Server), or do I need to modify settings in SQL (2000), or what?
>> >>
>> >> Thanks
>> >>
>> >
>> >
>>
>

Got my SQLIO Results... Now what?

I posted a similar message to SQL Tools group, apologize for the
double-post, but this is urgent...
I ran SQLIO against my SAN and got back some encouraging results. It
basically seemns to point to optimal performance settings of 64KB I/O's,
4096KB "stripes", with the Buffering option to "All". How can I make sure
Windows, SQL, et al. are using the same options that SQLIO used? Is there
something that needs to be set in the registry to ensure 64KB I/O's/4096KB
"stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do I
need to modify settings in SQL (2000), or what?
ThanksIn SQL 2000 you do not have to set any knobs.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.yomomma.com> wrote in message
news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>I posted a similar message to SQL Tools group, apologize for the
>double-post, but this is urgent...
> I ran SQLIO against my SAN and got back some encouraging results. It
> basically seemns to point to optimal performance settings of 64KB I/O's,
> 4096KB "stripes", with the Buffering option to "All". How can I make sure
> Windows, SQL, et al. are using the same options that SQLIO used? Is there
> something that needs to be set in the registry to ensure 64KB I/O's/4096KB
> "stripes"/Buffering=All, or somewhere else in Windows (2003 Server), or do
> I need to modify settings in SQL (2000), or what?
> Thanks
>|||That's good to know. Now I need to know I *can* do with the SQLIO results.
So far the only use I've found is to print out hard copies so I'll always
have something handy to wrap raw fish with.
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
> In SQL 2000 you do not have to set any knobs.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.yomomma.com> wrote in message
> news:%239MLdDNKFHA.904@.tk2msftngp13.phx.gbl...
>|||SQLIO results can be useful if you experience IO bottleneck in SQL Server.
You should collect windows and SQL server performance counters as well if
you suspect such a problem.
The primary solution to IO problems are: 1)configure files and filegroups in
your database so that you can use your existing disk volumes better; 2) get
better hardware. SQL Server automatically issues the optimal IO size depends
on your file/filegroup configuration.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:E3BZd.4121$So4.369@.fe10.lga...
> That's good to know. Now I need to know I *can* do with the SQLIO
results.
> So far the only use I've found is to print out hard copies so I'll always
> have something handy to wrap raw fish with.
> "wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
> news:eteDqvTKFHA.3336@.TK2MSFTNGP09.phx.gbl...
I/O's,[vbcol=seagreen]
sure[vbcol=seagreen]
(2003[vbcol=seagreen]
>|||Thanks for the feedback Wei. Unfortunately we've got top of the line
hardware and have configured the heck out of files and filegroups in the
database. In fact, SQL Server isn't the issue at all. We eliminated SQL
Server completely as a bottleneck. But my understanding was that SQLIO
measures the speed of the Disk I/O subsystem, not SQL Server, anyway.
Perhaps I read the documentation wrong. Anyways, I've decided that using
SQLIO is a dead end, so I'm on to other tools. Thanks.
"Wei Xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:423cb5b4$1@.news.microsoft.com...
> SQLIO results can be useful if you experience IO bottleneck in SQL Server.
> You should collect windows and SQL server performance counters as well if
> you suspect such a problem.
> The primary solution to IO problems are: 1)configure files and filegroups
> in
> your database so that you can use your existing disk volumes better; 2)
> get
> better hardware. SQL Server automatically issues the optimal IO size
> depends
> on your file/filegroup configuration.
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:E3BZd.4121$So4.369@.fe10.lga...
> results.
> I/O's,
> sure
> (2003
>

Monday, March 12, 2012

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?
"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David