I need to start training someone on SQL Server 2000, but do not want them to
be able to modify any data or structures. Is there a better way of creating
a server wide read only account besides assigning them to the db_datareader
role in every database?
Thanks.Hi,
There is no server side role for this, Alternate approach is given below,
1. Create a Login named 'reader'
2. Create the user named 'reader' in Model database
3. Assign db_datareader role to 'reader' user
THis will create a reader user for all the new databases.
Note:
You will have to manually create this user in all existing databases.
Thanks
Hari
MCDBA
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>|||better still, why dont you install a msde training instance on a new box
(preferably) and give them sysadmin to it, you could then make a copy of
live data from one to the other and not worry that they are going to destroy
something valuable, and still provide them with the full access that they
are going to eventually have (one day) on the "live" instance.
Br,
Mark Broadbent
mcdba,mcse+i
=======================
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>
Showing posts with label training. Show all posts
Showing posts with label training. Show all posts
Wednesday, March 21, 2012
Creating a server wide read only account
I need to start training someone on SQL Server 2000, but do not want them to
be able to modify any data or structures. Is there a better way of creating
a server wide read only account besides assigning them to the db_datareader
role in every database?
Thanks.Hi,
There is no server side role for this, Alternate approach is given below,
1. Create a Login named 'reader'
2. Create the user named 'reader' in Model database
3. Assign db_datareader role to 'reader' user
THis will create a reader user for all the new databases.
Note:
You will have to manually create this user in all existing databases.
Thanks
Hari
MCDBA
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>|||better still, why dont you install a msde training instance on a new box
(preferably) and give them sysadmin to it, you could then make a copy of
live data from one to the other and not worry that they are going to destroy
something valuable, and still provide them with the full access that they
are going to eventually have (one day) on the "live" instance.
--
Br,
Mark Broadbent
mcdba,mcse+i
======================="Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>sql
be able to modify any data or structures. Is there a better way of creating
a server wide read only account besides assigning them to the db_datareader
role in every database?
Thanks.Hi,
There is no server side role for this, Alternate approach is given below,
1. Create a Login named 'reader'
2. Create the user named 'reader' in Model database
3. Assign db_datareader role to 'reader' user
THis will create a reader user for all the new databases.
Note:
You will have to manually create this user in all existing databases.
Thanks
Hari
MCDBA
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>|||better still, why dont you install a msde training instance on a new box
(preferably) and give them sysadmin to it, you could then make a copy of
live data from one to the other and not worry that they are going to destroy
something valuable, and still provide them with the full access that they
are going to eventually have (one day) on the "live" instance.
--
Br,
Mark Broadbent
mcdba,mcse+i
======================="Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>sql
Monday, March 19, 2012
Creating a random password.
I'm new to SQL and have no training. Just trying to learn this on my
own.
I've got a system that grabs new users from 1 table and inserts them
into a user table for a diff. system. I need to be able to assign them
a random password. I've got code that will do this using NEWID() and
RAND() functions and works fine in Query analyser. But if I try to
write a function, I find I can't have NEWID() and RAND() in a UDF. I
can write this as a Procedure, but can't figure out complete syntax of
Proc. or how to call it in my SQL update command.
SQL update command is:
UPDATE clientaccess SET password= dbo.makepassword()
WHERE password = ''
of course, that's calling it as a function. How do I call a Proc in
same UPDATE code?
Here's code as function. How would I change to proc and call?
CREATE function dbo.GenerateRandomString ()
RETURNS char(8)
As
Begin
declare @.password char(8)
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = ''
while @.count <= 8
begin
set @.string = @.string +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
RETURN(@.password)
endTry putting the UPDATE in the proc definition and passing the account and
password as parameters. Note however that the end-user should change the
password (sp_password) ASAP as this approach is not very secure. Also, just
as an added "slight" layer of security , I'd recommend you use WITH
ENCRYPTION for the proc.
HTH
Jerry
<jfeldbruegge@.yahoo.com> wrote in message
news:1129045975.003145.224220@.g44g2000cwa.googlegroups.com...
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>|||Hi
NewID is a nondeterministic function and can't be used in a function, RAND
seems to cause issues even though it has a seed. To overcome this problems
you could pass a NEWID and Random number to the function, but that would be
less secure than using a different one for each character.
One solution when using a procedure is to loop or cursor for each row to
update:
CREATE PROCEdURE dbo.GenerateRandomString (@.password VARCHAR(8) OUTPUT)
AS
BEGIN
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = NULL
while @.count <= 8
begin
set @.password = ISNULL(@.password,'') +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
END
-- Example table
CREATE TABLE clientaccess ( username varchar(40) NOT NULL, password char(8)
NOT NULL DEFAULT ('') )
-- Example data
INSERT INTO clientaccess ( username ) values ( 'john' )
INSERT INTO clientaccess ( username ) values ( 'jim' )
INSERT INTO clientaccess ( username ) values ( 'jack' )
SELECT * FROM clientaccess
DECLARE @.password varchar(8), @.username varchar(40)
DECLARE user_cursor CURSOR FOR
SELECT username
FROM clientaccess
WHERE ISNULL(password,'') = ''
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.password = ''
EXEC dbo.GenerateRandomString @.password OUTPUT
UPDATE clientaccess
SET password = @.password
WHERE CURRENT OF user_cursor
FETCH NEXT FROM user_cursor INTO @.username
END
CLOSE user_cursor
DEALLOCATE user_cursor
GO
SELECT * FROM clientaccess
John
"jfeldbruegge@.yahoo.com" wrote:
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>
own.
I've got a system that grabs new users from 1 table and inserts them
into a user table for a diff. system. I need to be able to assign them
a random password. I've got code that will do this using NEWID() and
RAND() functions and works fine in Query analyser. But if I try to
write a function, I find I can't have NEWID() and RAND() in a UDF. I
can write this as a Procedure, but can't figure out complete syntax of
Proc. or how to call it in my SQL update command.
SQL update command is:
UPDATE clientaccess SET password= dbo.makepassword()
WHERE password = ''
of course, that's calling it as a function. How do I call a Proc in
same UPDATE code?
Here's code as function. How would I change to proc and call?
CREATE function dbo.GenerateRandomString ()
RETURNS char(8)
As
Begin
declare @.password char(8)
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = ''
while @.count <= 8
begin
set @.string = @.string +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
RETURN(@.password)
endTry putting the UPDATE in the proc definition and passing the account and
password as parameters. Note however that the end-user should change the
password (sp_password) ASAP as this approach is not very secure. Also, just
as an added "slight" layer of security , I'd recommend you use WITH
ENCRYPTION for the proc.
HTH
Jerry
<jfeldbruegge@.yahoo.com> wrote in message
news:1129045975.003145.224220@.g44g2000cwa.googlegroups.com...
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>|||Hi
NewID is a nondeterministic function and can't be used in a function, RAND
seems to cause issues even though it has a seed. To overcome this problems
you could pass a NEWID and Random number to the function, but that would be
less secure than using a different one for each character.
One solution when using a procedure is to loop or cursor for each row to
update:
CREATE PROCEdURE dbo.GenerateRandomString (@.password VARCHAR(8) OUTPUT)
AS
BEGIN
declare @.choices varchar(100)
declare @.count int
set @.choices = ''
-- load up numbers 0 - 9
set @.count = 48
while @.count <=57
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
-- load up lowercase letters a - z
set @.count = 97
while @.count <=122
begin
set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
set @.count = @.count + 1
end
set @.count = 0
set @.password = NULL
while @.count <= 8
begin
set @.password = ISNULL(@.password,'') +
SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
as int)%LEN(@.choices)+1,1)
set @.count = @.count + 1
end
END
-- Example table
CREATE TABLE clientaccess ( username varchar(40) NOT NULL, password char(8)
NOT NULL DEFAULT ('') )
-- Example data
INSERT INTO clientaccess ( username ) values ( 'john' )
INSERT INTO clientaccess ( username ) values ( 'jim' )
INSERT INTO clientaccess ( username ) values ( 'jack' )
SELECT * FROM clientaccess
DECLARE @.password varchar(8), @.username varchar(40)
DECLARE user_cursor CURSOR FOR
SELECT username
FROM clientaccess
WHERE ISNULL(password,'') = ''
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @.username
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.password = ''
EXEC dbo.GenerateRandomString @.password OUTPUT
UPDATE clientaccess
SET password = @.password
WHERE CURRENT OF user_cursor
FETCH NEXT FROM user_cursor INTO @.username
END
CLOSE user_cursor
DEALLOCATE user_cursor
GO
SELECT * FROM clientaccess
John
"jfeldbruegge@.yahoo.com" wrote:
> I'm new to SQL and have no training. Just trying to learn this on my
> own.
> I've got a system that grabs new users from 1 table and inserts them
> into a user table for a diff. system. I need to be able to assign them
> a random password. I've got code that will do this using NEWID() and
> RAND() functions and works fine in Query analyser. But if I try to
> write a function, I find I can't have NEWID() and RAND() in a UDF. I
> can write this as a Procedure, but can't figure out complete syntax of
> Proc. or how to call it in my SQL update command.
> SQL update command is:
> UPDATE clientaccess SET password= dbo.makepassword()
> WHERE password = ''
> of course, that's calling it as a function. How do I call a Proc in
> same UPDATE code?
> Here's code as function. How would I change to proc and call?
> CREATE function dbo.GenerateRandomString ()
> RETURNS char(8)
> As
> Begin
> declare @.password char(8)
> declare @.choices varchar(100)
> declare @.count int
> set @.choices = ''
> -- load up numbers 0 - 9
> set @.count = 48
> while @.count <=57
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> -- load up lowercase letters a - z
> set @.count = 97
> while @.count <=122
> begin
> set @.choices = @.choices + Cast(CHAR(@.count) as char(1))
> set @.count = @.count + 1
> end
> set @.count = 0
> set @.password = ''
> while @.count <= 8
> begin
> set @.string = @.string +
> SUBSTRING(@.choices,CAST(ABS(CHECKSUM(NEW
ID()))*RAND(@.count)
> as int)%LEN(@.choices)+1,1)
> set @.count = @.count + 1
> end
> RETURN(@.password)
> end
>
Subscribe to:
Posts (Atom)