"simple" security?

I've heard that SQL Server's security system is good, but complicated. What
is the easiest way to:
1. Limit who can make structural changes to the database, including stored
procedures.
2. Limit read access to certain tables or fields, and limit write access for
others.
At this point, I don't need something totally bulletproof. Just plain
serviceable would do for the time being.There are some database roles called db_datareader and db_datawriter that
allow a user to either read or write to tables. Apart from those, you could
just not grant any rights at all to a user, but give him explicit SELECT,
INSERT, UPDATE, DELETE permissions on a table. You can find a brief overview
of SQL Server security model at:
http://vyaskn.tripod.com/sql_server...t_practices.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:uaZAPfjXFHA.2420@.TK2MSFTNGP12.phx.gbl...
> I've heard that SQL Server's security system is good, but complicated.
What
> is the easiest way to:
> 1. Limit who can make structural changes to the database, including stored
> procedures.
> 2. Limit read access to certain tables or fields, and limit write access
for
> others.
>
> At this point, I don't need something totally bulletproof. Just plain
> serviceable would do for the time being.
>
>
>|||Thank you. I will try to find something "simple" in there...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23ZEoqijXFHA.3732@.TK2MSFTNGP10.phx.gbl...
> There are some database roles called db_datareader and db_datawriter that
> allow a user to either read or write to tables. Apart from those, you
> could
> just not grant any rights at all to a user, but give him explicit SELECT,
> INSERT, UPDATE, DELETE permissions on a table. You can find a brief
> overview
> of SQL Server security model at:
> http://vyaskn.tripod.com/sql_server...t_practices.htm
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:uaZAPfjXFHA.2420@.TK2MSFTNGP12.phx.gbl...
> What
> for
>

"limit" in sql server

someone suggested I use:

select top 20 * from t
where pkc not in (select top 10 pkc from t order by pkc) order by pkc

to simulate the limit function in mysql.

i want to replace the 10 in the inner select with a variable. When I do this:

select top 20 * from t
where pkc not in (select top @.counter pkc from t order by pkc) order by pkc

it gives me in an error in sproc. Pls help!!It needs to be dynamic

DECLARE @.SQL varchar(8000), @.Counter int
SELECT @.Counter = 10
SELECT @.SQL = 'select top 20 * from t'
+ ' where pkc not in (select top '
+ CONVERT(varchar(15),@.counter)
+ ' pkc from t order by pkc) order by pkc'
SELECT @.SQL
EXEC(@.SQL)

"Limit to List" equivalent in SQL

In Access, I can limit field options to a list. What is the equivalent in SQL 2005?use a lookup table and enforce referential integrity.|||well, if it's a limited set, with ~0 likelihood of needing to be updated, you can also use a CHECK constraint

for examplecreate table foo
( ...
, gender char(1) null check (coalesce(gender,'M') in ('M','F'))
... )

"Know Issue" not in KB. COLID continues to rise.

I use transactional replication on several very "wide" table. One has
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

"Know Issue" not in KB. COLID continues to rise.

I use transactional replication on several very "wide" table. One has
254 columns. After finding that was my limit for replication to work I
removed an unused columns and added a new needed column. Since the
COLID in the SYSCOLUMNS did not reset replication failed. I'm down to
renaming columns now.
This was in SQL Server 2000 and is still present in 2005. A call to
Microsoft turned up "Known Issue" statement.
Has anyone found another way around this without creating a new table
with the needed columns and moving the current data in? Keep in mind I
inherited this database and did not create a table this wide.
Thanks.
Mike.Mike,
apart from your suggestion, the only other workaround I am aware of is to
partition the table and to replicate the 2 tables separately, which is
probably more work than your method.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

"IN" clause limitation

Does anybody know what is the limit for the number of values one can
have in a list of expressions (to test for a match) for the IN clause?

For example,
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')

Is there a limit in the number of expressions that can be included in
the list to check for a match to STATE? I know this is a bad example as
there are only 50 states, but my actual query is similar to this, the
only difference being I may have about 1000 values in the 'IN' clause
to test for a match.

TIA!I don't think there is a documented limit and with 1000 values you'll
probably be ok. At some point you'll hit a memory allocation or batch
size limit if you keep adding values to the list.

Is that really the best way you can pass 1000 values? Why don't you put
the data in a table instead?

--
David Portas
SQL Server MVP
--|||Thanks David! You were right on target - I've already hit the memory
allocation limit!

Table sounds good, but I was just tweaking somebody else's code. Thanks
for the idea though! :)

David Portas wrote:
> I don't think there is a documented limit and with 1000 values you'll
> probably be ok. At some point you'll hit a memory allocation or batch
> size limit if you keep adding values to the list.
> Is that really the best way you can pass 1000 values? Why don't you
put
> the data in a table instead?
> --
> David Portas
> SQL Server MVP
> --|||karthik (karthiksmiles@.gmail.com) writes:
> Does anybody know what is the limit for the number of values one can
> have in a list of expressions (to test for a match) for the IN clause?
> For example,
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> Is there a limit in the number of expressions that can be included in
> the list to check for a match to STATE? I know this is a bad example as
> there are only 50 states, but my actual query is similar to this, the
> only difference being I may have about 1000 values in the 'IN' clause
> to test for a match.

1000 values should work. However, the performance for large IN list
is absymal. The problem is not with query execution time, but query
compile time. So putting the values into a table is certainly a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp