Showing posts with label exec. Show all posts
Showing posts with label exec. Show all posts

Thursday, March 8, 2012

Convert SQL Account to Windows Accounts + EXEC on ALL SPs

I am no DBA, but this is my task.

I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".

I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?

What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.

Any suggestions would be greatly appreciated!DaBrain (Tapplication@.gmail.com) writes:

Quote:

Originally Posted by

I am no DBA, but this is my task.
>
I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".
>
I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?
>
What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.


First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!

Whatever, I recommend that you create a role, and then add all Windows
logins to that role:

exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'

(I may have misremembered the order of the paramerers to sp_addrolemember.)

Then you can grant access to the procedures to the role:

SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'

As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi

DaBrain, will also have to configure all the client applications to use
Windows authentication instead of SQL Server Authentication. (or instruct
the users as to how to do it).

Depending on the number of users, the application they are using, this might
be a time-c0nsuming task.

If they connect through Access XP ADPs this is a relatively simple task.

If they connect using a DSN it will be slightly more difficult.

If they connect through some custom application where the connection info is
stored in a .INI file or the registry and the developers didn't plan on
connecting with Windows authentication this might be somewhere between
difficult and impossible.

--
-Dick Christoph

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns990D92FA5FA6Yazorman@.127.0.0.1...

Quote:

Originally Posted by

DaBrain (Tapplication@.gmail.com) writes:

Quote:

Originally Posted by

>I am no DBA, but this is my task.
>>
>I have an SQL Server 2000 Database that has an "SQL Account" that has
>execute permission on all Stored procedures. it is what was used by
>the company. This one account is used by "all workstations".
>>
>I want to fix this and use Windows Accounts, and get rid of that SQL
>Account. How do I go about adding that Windows Account permission to
>all the Stored Procedures?
>>
>What I want to do is to just add several windows account then go about
>removing the permission where necessary on an account by account
>basis.


>
First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!
>
Whatever, I recommend that you create a role, and then add all Windows
logins to that role:
>
exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'
>
(I may have misremembered the order of the paramerers to
sp_addrolemember.)
>
Then you can grant access to the procedures to the role:
>
SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'
>
As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 7, 2012

Convert SQL 2000 Script to SQL Server 2005

Help me convert this script to SQL Server 2005:
use master
go
exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
go
grant all on dbo.xp_getsequence to public
go
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.newid INT
SELECT @.seqname = db_name() + '..' + @.seqname
EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
RETURN(@.newid)
END
go
grant all on system_function_schema.fn_getsequence to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF @.inputvalue is null
SELECT @.rtnvalue='.'
ELSE
BEGIN
IF LTRIM(RTRIM(@.inputvalue))=''
SELECT @.rtnvalue='.'
ELSE
SELECT @.rtnvalue=@.inputvalue
END
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_blank2dot to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF LTRIM(RTRIM(@.inputvalue))='.'
SELECT @.rtnvalue=''
ELSE
SELECT @.rtnvalue=@.inputvalue
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_dot2blank to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat2 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
VARCHAR(500),@.str3 VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat3 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
sql_variant)
RETURNS sql_variant AS
BEGIN
RETURN (isnull(@.inputvalue,@.replacement))
END
go
grant all on system_function_schema.fn_nvl to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_tochar(@.inputvalue sql_variant)
RETURNS VARCHAR(500) AS
BEGIN
RETURN CAST(@.inputvalue AS VARCHAR)
END
go
grant all on system_function_schema.fn_tochar to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
VARCHAR(10),@.length INT)
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.newseqid VARCHAR(50)
SELECT @.prefix = coalesce(@.prefix, '')
SELECT @.length = coalesce(@.length, 0)
SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
- len(@.newseqid)) + @.newseqid
RETURN(@.newseqid)
END
go
grant all on system_function_schema.fn_getfullseq to public
go
CREATE PROCEDURE ReloadG
AS
EXEC xp_cmdshell 'START Command ', no_output
go
grant execute on master..ReloadG to public
goM P
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
What if the user provides NULL for one of the parameters?
Visit www.projectdmx.com/tsql/
"M P" <mponteres@.gmail.com> wrote in message
news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> Help me convert this script to SQL Server 2005:
> use master
> go
> exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> go
> grant all on dbo.xp_getsequence to public
> go
> EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> RECONFIGURE WITH OVERRIDE
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> RETURNS INT AS
> BEGIN
> DECLARE @.newid INT
> SELECT @.seqname = db_name() + '..' + @.seqname
> EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> RETURN(@.newid)
> END
> go
> grant all on system_function_schema.fn_getsequence to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF @.inputvalue is null
> SELECT @.rtnvalue='.'
> ELSE
> BEGIN
> IF LTRIM(RTRIM(@.inputvalue))=''
> SELECT @.rtnvalue='.'
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> END
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_blank2dot to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF LTRIM(RTRIM(@.inputvalue))='.'
> SELECT @.rtnvalue=''
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_dot2blank to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat2 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> VARCHAR(500),@.str3 VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat3 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> sql_variant)
> RETURNS sql_variant AS
> BEGIN
> RETURN (isnull(@.inputvalue,@.replacement))
> END
> go
> grant all on system_function_schema.fn_nvl to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_tochar(@.inputvalue sql_variant)
> RETURNS VARCHAR(500) AS
> BEGIN
> RETURN CAST(@.inputvalue AS VARCHAR)
> END
> go
> grant all on system_function_schema.fn_tochar to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> VARCHAR(10),@.length INT)
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.newseqid VARCHAR(50)
> SELECT @.prefix = coalesce(@.prefix, '')
> SELECT @.length = coalesce(@.length, 0)
> SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> - len(@.newseqid)) + @.newseqid
> RETURN(@.newseqid)
> END
> go
> grant all on system_function_schema.fn_getfullseq to public
> go
> CREATE PROCEDURE ReloadG
> AS
> EXEC xp_cmdshell 'START Command ', no_output
> go
> grant execute on master..ReloadG to public
> go
>|||I am not familiar with the script. I have a task in checking for possibility
of this script to be used on SQL Server 2005. Can you help?
"Uri Dimant" wrote:
> M P
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> What if the user provides NULL for one of the parameters?
> Visit www.projectdmx.com/tsql/
>
> "M P" <mponteres@.gmail.com> wrote in message
> news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> > Help me convert this script to SQL Server 2005:
> >
> > use master
> > go
> > exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> > go
> > grant all on dbo.xp_getsequence to public
> > go
> >
> > EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> > RECONFIGURE WITH OVERRIDE
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.newid INT
> > SELECT @.seqname = db_name() + '..' + @.seqname
> > EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> > RETURN(@.newid)
> > END
> > go
> > grant all on system_function_schema.fn_getsequence to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF @.inputvalue is null
> > SELECT @.rtnvalue='.'
> > ELSE
> > BEGIN
> > IF LTRIM(RTRIM(@.inputvalue))=''
> > SELECT @.rtnvalue='.'
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > END
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_blank2dot to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF LTRIM(RTRIM(@.inputvalue))='.'
> > SELECT @.rtnvalue=''
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_dot2blank to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat2 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500),@.str3 VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat3 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> > sql_variant)
> > RETURNS sql_variant AS
> > BEGIN
> > RETURN (isnull(@.inputvalue,@.replacement))
> > END
> > go
> > grant all on system_function_schema.fn_nvl to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_tochar(@.inputvalue sql_variant)
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > RETURN CAST(@.inputvalue AS VARCHAR)
> > END
> > go
> > grant all on system_function_schema.fn_tochar to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> > VARCHAR(10),@.length INT)
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.newseqid VARCHAR(50)
> > SELECT @.prefix = coalesce(@.prefix, '')
> > SELECT @.length = coalesce(@.length, 0)
> > SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> > SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> > - len(@.newseqid)) + @.newseqid
> >
> > RETURN(@.newseqid)
> > END
> > go
> > grant all on system_function_schema.fn_getfullseq to public
> > go
> >
> > CREATE PROCEDURE ReloadG
> > AS
> > EXEC xp_cmdshell 'START Command ', no_output
> > go
> > grant execute on master..ReloadG to public
> > go
> >
>
>