Persistent autoincrementing value, not attached to row insertion?

I need to get a unique value to use for a record *before* the record is added to a table. It doesn't have to be contiguous with existing records, but it must always be unique, has to be persistent over multiple instantiations of an ASP.NET application, and has to work in that sort of a multi-session environment (where other sessions could need additional unique values before the first session gets around to actually adding a record to the table).

I considered generating and using unique CLSIDs for this, but the resulting value also needs to become part of the filename of some files that are being saved to the disk (and those names also saved in the table), and including text CLSIDs along with other filename data would make for some unpleasantly long and difficult to work with filenames.

I also don't think there's a practical way for me to use a trigger associated with an identity column for this, because I need to save files to disk using the unique value before I even know if the record will in fact end up being added to the table, and what's more, the numbers and names of those files will vary in ways that might be difficult to handle in a stored procedure.

What I'm thinking I will have to do is create a separate database table called something like "UniqueIDGen". This table would have a single record in it with a single integer value, initialized to a value of 1. Then, each time an ID is needed, this one record would be locked, read and incremented by 1. The only reason for doing it this way instead of with an application variable, as I see it, is that the values need to be unique and continue incrementing in perpetuity, no matter how many times the ASP.NET application is recycled or the server is rebooted.

But I still have to wonder if there might be a more efficient method provided by SQL Server for this type of unique value generation ... something that is equally as persistent without requiring an entire table with only a single record to be allocated to such a basic task. Does anyone know of a more elegant solution for this?do a hash of the current date.time.milliseconds, or a unix timestamp in the backend of the asp.net, unless you plan on data being entered in the same second.|||Is there no better option based upon SQL Server or some other persistent technology, other than the workable but awkward solution I mentioned? For some reason I thought there might be a non-table-based identity value, or something like that, specifically for situations like this. Perhaps that is something I'm remembering from some other RDBMS software I dealt with in the past, and not SQL Server?|||Hi,

You could do this, it is a bit like Oracle's NextVal:

CREATE TABLE dbo.Sequences
(
ID int NOT NULL
)

INSERT INTO Sequences (ID) VALUES (0)

CREATE PROCEDURE dbo.NextValue
@.ID int output
AS
UPDATE Sequences SET ID = ID + 1, @.ID = ID + 1
RETURN

And call the stored proc to get the ID.

private void Button1_Click(object sender, System.EventArgs e)
{
string connectionString = @."Server=wpeude-masonix2;Database=TestDatabase;User ID=sa;Password=sa;Trusted_Connection=False";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "dbo.NextValue";
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@.ID", SqlDbType.Int, 4);
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);
connection.Open();
command.ExecuteNonQuery();
int pk = (int)param.Value;
connection.Close();
lblNextValue.Text = pk.ToString();
}

The ID would be unique in the Database. You could change the sproc to use NEWID() and have a varchar column instead, if you wanted to use a GUID.

A.|||asmason,

Your example is precisely the solution I proposed in my original post (unless I did an insufficient job of describing what I was thinking of). If there's no method available that's superior to creating a table for this purpose, then I'll go ahead and do it that way.

Thanks for taking the time to write it up.|||Couldn't you take and make a stored procedure that makes it for you? Basically when you go to insert a value you do a :

select @.IDVariable = Max(IDField) from Table

Then when you insert:

 Insert into Table (IDField) Values (@.IDVariable)

I have done something very similar to this in several projects and it remains unique and it is available when you create it.|||Regarding the suggestion of:

select @.IDVariable = Max(IDField) from Table

... and then ...

Insert into Table (IDField) Values (@.IDVariable)

I don't think I should do that. In my case, fair amount of time (seconds or even minutes) can potentially elapse between the time I need the unique ID (the first line) and the actual INSERT (the second line). What happens when, when session #1 is sitting at some point in between those two statements, and session #2 comes along and performs the first statement to get an ID for itself? It will get the same one that the first session got ... and then. if both sessions end up inserting a new row using the provided ID ... *biff*, collisions.

In fact, generally it seems like this wouldn't be wholly safe in any multi-threaded environment (even a delay of milliseconds could theoretically lead to a collision, though it might be much less likely than in my current project). Maybe it would be okay if both statements were encased within a single transaction, but that doesn't allow the ID value to be used for things outside of the database, which is a requirement of what I'm working on.

Also, I can only assume that the performance of the MAX function degrades as the number of rows in the table increases, so that may be a bit of a negative as well, at least compared to the standalone "ID table" method.

For what it is worth, I've already gone ahead and implemented a table for this task, very similar to the code that asmason included above, and it works very nicely. I did add one thing: a "counter_name" field, so that the same table could be used to maintain additional unique counters or values if I run across similiar needs again in the future. Thus, the statement in my stored procedure looks like this:

UPDATE AppCounters SET id = id + 1, @.RETURNID = id + 1 WHERE counter_name = @.ctrname

permissions gone missing in sysprotects

Hi there,
I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
noticed that load of records have gone missing in sysprotects table (
something like from 5000 records down to 60 records only).
As a results, permission setting does not appear in objects permission
management in Enterprise Manager.
I know that the permission setting is still hold in syspermissions table,
but without the "green ticks" appears on the Enterprise manager interface, w
e
would not be able to know what permission we have assigned to each role or
user at all.
This problem only happen with the database that has been moved from 7.0 to
2000 though , if you move db from 2000 to 2000, it seems to be fine.
Does anyone else out there know how to fix this ?
cheers
JackI have never heard of this one.
One way I could see having problems is if you had modified
system tables. That's the only scenario I can think of but I
would have thought you'd get an error in the upgrade
process.
As already suggested in one of the other groups, you should
contact Microsoft Product Support if you feel you have a
bug.
-Sue
On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
<JackYao@.discussions.microsoft.com> wrote:

>Hi there,
>I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
>noticed that load of records have gone missing in sysprotects table (
>something like from 5000 records down to 60 records only).
>As a results, permission setting does not appear in objects permission
>management in Enterprise Manager.
>I know that the permission setting is still hold in syspermissions table,
>but without the "green ticks" appears on the Enterprise manager interface,
we
>would not be able to know what permission we have assigned to each role or
>user at all.
>This problem only happen with the database that has been moved from 7.0 to
>2000 though , if you move db from 2000 to 2000, it seems to be fine.
>Does anyone else out there know how to fix this ?
>cheers
>Jack|||Hi,
By any chance were these permissions on system objects in master.dbo?
AFAIK these were never preserved during upgrades.
Note - In Yukon, we now preserve permissions on system objects during the
upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshell
,
we will remember that during the upgrade.
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
"Sue Hoegemeier" wrote:

> I have never heard of this one.
> One way I could see having problems is if you had modified
> system tables. That's the only scenario I can think of but I
> would have thought you'd get an error in the upgrade
> process.
> As already suggested in one of the other groups, you should
> contact Microsoft Product Support if you feel you have a
> bug.
> -Sue
> On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
> <JackYao@.discussions.microsoft.com> wrote:
>
>|||Hi Clifford ,
the missing permissions are not in the sysprotects in Master db either. Bear
in mind that these missing permissions are set specificly to the user
assigned to my production database, and these users do not exist in Master
database anyway.
As I said before , I noticed that, everytime I set permission to the
production database, it only goes into syspermissions, so I wrote this scrip
t
to list out permission on my production database, and it works fine.
********************
select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
case actadd
when 1 then 'SELECT ONLY'
when 2 then 'UPDATE ONLY'
when 3 then 'SELECT + UPDATE'
when 4 then 'DRI'
when 5 then 'SELECT + DRI'
when 8 then 'INSERT ONLY'
when 9 then 'SELECT + INSERT'
when 27 then 'SEL+INST+UPDT+DEL'
when 31 then 'SEL+INST+UPDT+DEL+DRI'
when 32 then 'SP EXECUTED'
END [PERMISSION]
from sysobjects
inner join syspermissions
on sysobjects.id = syspermissions.id
inner join sysusers
on sysusers.uid = syspermissions.grantee
and sysusers.name = 'myusername'
order by objects
***********************
So even though I cannot see the green tick in EM interface, I can still see
the setting permissions from that scripts.
Still, there is no way to resolve this issue, as far as I know anyway .. :-(
Jack
"Clifford Dibble" wrote:
[vbcol=seagreen]
> Hi,
> By any chance were these permissions on system objects in master.dbo?
> AFAIK these were never preserved during upgrades.
> Note - In Yukon, we now preserve permissions on system objects during the
> upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshe
ll,
> we will remember that during the upgrade.
> Regards,
> Clifford Dibble
> Program Manager
> SQL Server Engine
>
> "Sue Hoegemeier" wrote:
>|||HI Jack,
Can you run the following query and report the result?
select id, uid, type from sysobjects where name = 'sysprotects'
select * from syscolumns where id = object_id('dbo.sysprotects')
Thanks
Andrew
SQL Server Engine
"Jack Yao" wrote:
[vbcol=seagreen]
> Hi Clifford ,
> the missing permissions are not in the sysprotects in Master db either. Be
ar
> in mind that these missing permissions are set specificly to the user
> assigned to my production database, and these users do not exist in Master
> database anyway.
> As I said before , I noticed that, everytime I set permission to the
> production database, it only goes into syspermissions, so I wrote this scr
ipt
> to list out permission on my production database, and it works fine.
> ********************
> select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
> case actadd
> when 1 then 'SELECT ONLY'
> when 2 then 'UPDATE ONLY'
> when 3 then 'SELECT + UPDATE'
> when 4 then 'DRI'
> when 5 then 'SELECT + DRI'
> when 8 then 'INSERT ONLY'
> when 9 then 'SELECT + INSERT'
> when 27 then 'SEL+INST+UPDT+DEL'
> when 31 then 'SEL+INST+UPDT+DEL+DRI'
> when 32 then 'SP EXECUTED'
> END [PERMISSION]
> from sysobjects
> inner join syspermissions
> on sysobjects.id = syspermissions.id
> inner join sysusers
> on sysusers.uid = syspermissions.grantee
> and sysusers.name = 'myusername'
> order by objects
> ***********************
> So even though I cannot see the green tick in EM interface, I can still se
e
> the setting permissions from that scripts.
> Still, there is no way to resolve this issue, as far as I know anyway .. :
-(
> Jack
>
> "Clifford Dibble" wrote:
>|||Hi Andrew,
sorry for the mess, it is difficult to show you the results of second query
in proper format since it has too many columns to show in this tiny space
here, but here we go:
the query select id, uid, type from sysobjects where name = 'sysprotects'
return the following results :
id uid type
23 1 S
the query "select * from syscolumns where id = object_id('dbo.sysprotects')"
return:
id 23 56 1 56 4 10 0 1 4 0 0 0 0 0 0 1 N
ULL 2 NULL -1553186121 0 56 7 NULL 1
0 0 0 0 0 NULL NULL
uid 23 52 1 52 2 5 0 2 8 0 0 0 0 0 0 2 N
ULL 6 NULL -1553186121 0 52 6 NULL 5
0 0 0 0 NULL NULL
action 23 48 1 48 1 3 0 3 10 0 0 0 0 0 0
3 NULL 8 NULL -1553186121 0 48 5 NU
LL 3 0 0 0 0 NULL NULL
protecttype 23 48 1 48 1 3 0 4 11 0 0 0
0 0 0 4 NULL 9 NULL -1553186121 0 48
5 NULL 3 0 0 0 0 NULL NULL
columns 23 165 2 165 4000 0 0 5 -1 0 0 0 0 0 0 5 NULL -1 NULL -1553186121 24
37 4 NULL 4000 NULL 0 0 1 NULL NULL
grantor 23 52 1 52 2 5 0 6 12 0 0 0 0 0
0 6 NULL 10 NULL -1553186121 0 52 6
NULL 5 0 0 0 0 NULL NULL
Please let me know how you go with it
Jack
"Andrew Zhu" wrote:
[vbcol=seagreen]
> HI Jack,
> Can you run the following query and report the result?
> select id, uid, type from sysobjects where name = 'sysprotects'
> select * from syscolumns where id = object_id('dbo.sysprotects')
> Thanks
> Andrew
> SQL Server Engine
> "Jack Yao" wrote:
>|||Hi Jack, I have experienced the same issue. On my site it was caused by
rights being granted to any object that has a type other than table,
view or proc. Use the following script to detect.
select b.name, b.type, a.* from syspermissions a, sysobjects b where
a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
order by b.type
To resolve, you must remove the offending syspermissions entries before
upgrading. As soon as SQL2000 the upgrade hits one of the problem
entries it stops populating sysprotects. I cant see any way to resolve
the issue after the 2000 upgrade.
Use the following script to delete the problem entries.
sp_configure 'allow updates', 1
go
reconfigure with override
go
delete from syspermissions where id in (select b.id from syspermissions
a, sysobjects b where a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
go
sp_configure 'allow updates', 0
go
reconfigure with override
go
Hope this helps.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
That is exactly what I was talking about , the reason why sysprotects stop
populating the records.
Sadly , my situation is in the limbo since I have already gone through the
upgrade and the records in sysprotects are long gone (so re-upgrade ain't
gonna do any good).
Nevertheless , for the love of SQL :-) , do you habppen to know why SQL
stop populating sysprotects as soon as it hits those offending permissions ?
I mean, what make granting permission to those objects offend SQL at the
beginning with ?
ta
Jack
"Derek" wrote:

> Hi Jack, I have experienced the same issue. On my site it was caused by
> rights being granted to any object that has a type other than table,
> view or proc. Use the following script to detect.
> select b.name, b.type, a.* from syspermissions a, sysobjects b where
> a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
> order by b.type
> To resolve, you must remove the offending syspermissions entries before
> upgrading. As soon as SQL2000 the upgrade hits one of the problem
> entries it stops populating sysprotects. I cant see any way to resolve
> the issue after the 2000 upgrade.
> Use the following script to delete the problem entries.
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> delete from syspermissions where id in (select b.id from syspermissions
> a, sysobjects b where a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
> go
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> Hope this helps.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Hi Jack, my guess is that it stops populating sysprotects because SQL
server isn't designed to allow the setting of permissions on objects
like triggers, constraints, primary keys etc. You can't do it through
Enterprise Manager on 7.0 or 2000.
I have traced the problem on our system to a bad script that allocated
all permissions to all objects. During the upgrade, SQL 2000 probably
doesn't know what to do with the offending permission records and just
stops the load of sysprotects, which in turn means no further records
will go in when new permissions are assigned after the upgrade.
If you have already upgraded to 2000, I guess you could fix it by
creating a new database and copying over the objects and data, and then
setting the permissions. That way you have a new sysprotects table.
Cheers
Derek
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
not sure what you mean by "create new database and copy over the objects and
data".
Did you mean copy it with the copy database wizard or use the old
fashion "backup/restore" manually ?
Jack
"Derek" wrote:

> Hi Jack, my guess is that it stops populating sysprotects because SQL
> server isn't designed to allow the setting of permissions on objects
> like triggers, constraints, primary keys etc. You can't do it through
> Enterprise Manager on 7.0 or 2000.
> I have traced the problem on our system to a bad script that allocated
> all permissions to all objects. During the upgrade, SQL 2000 probably
> doesn't know what to do with the offending permission records and just
> stops the load of sysprotects, which in turn means no further records
> will go in when new permissions are assigned after the upgrade.
> If you have already upgraded to 2000, I guess you could fix it by
> creating a new database and copying over the objects and data, and then
> setting the permissions. That way you have a new sysprotects table.
> Cheers
> Derek
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Permission to view system tables in master database

I am trying to avoid the "Permission denied" error message when a proc tries
to select records from one of the system tables in a SQL Server 2005 master
database.
Sepcifically I need to be able to capture the IP address of the calling user
which I can get from sys.dm_exec_connections.
I have struggled with this for some time and could not find anything
directly on point in MSDN. However, I was able to cobble something together
that "appears" to work as shown below.
I am hoping that someone who has specific experience with this issue might
be able to comment on my approach and point out any issues or deficiencies.
Thanks
Dave
/*
Creating a proc that can view server state
1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
2. Create user for special login in the User DB
3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
4. Create a cerificate in the User DB
5. Sign the proc with the cerificate
6. Create the cerificate from backup in the Master DB
7. Create an authorization login mapped to the certificate in the Master DB
8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
9. Grant EXECUTE perms on the proc to users in the User DB
*/
--0. Create a test database
USE master
CREATE DATABASE testcert
GO
--1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
CREATE LOGIN myViewUser WITH PASSWORD = '!Pa55word!' ;
GO
USE master
GO
GRANT VIEW ANY DEFINITION TO myViewUser ;
GO
GRANT VIEW SERVER STATE TO myViewUser ;
GO
--
--2. Create user for special login in the User DB
USe testCert
GO
CREATE USER myViewUser FOR LOGIN myViewUser ;
GO
--
--3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
IF object_id('myPROC') IS NOT NULL
DROP PROCEDURE myPROC
GO
CREATE PROCEDURE myPROC
WITH EXECUTE AS 'myViewUser'
AS
BEGIN
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
END
GO
--
--Test: cannot execute - The user does not have permission to perform this
action.
EXEC myPROC
--4. Create a cerificate in the User DB
CREATE CERTIFICATE myCERT
ENCRYPTION BY PASSWORD = '!Pa55word!'
WITH SUBJECT = 'Test cert',
EXPIRY_DATE = '12/05/2010';
GO
BACKUP CERTIFICATE myCERT TO FILE = 'myCERT.cer' ;
--5. Sign the proc with the cerificate
ADD SIGNATURE TO myPROC
BY CERTIFICATE myCERT
WITH PASSWORD = '!Pa55word!'
GO
--6. Create the cerificate from backup in the Master DB
USE master --Permissions at the server scope can only be granted when the
current database is master
CREATE CERTIFICATE myCert FROM FILE = 'myCERT.cer'
--7. Create an authorization login mapped to the certificate in the Master D
B
CREATE LOGIN myAuthLogin
FROM CERTIFICATE mycert ;
GO
--
--8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
-- WIEW permissions are server-level permissions, grant AUTHENTICATE SERVER
to certificate mapped login
GRANT AUTHENTICATE SERVER TO myAuthLogin
GO
--test
USE testCert
--I can execute proc
EXEC myPROC
--create some users
CREATE LOGIN testuser1 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser1 FOR LOGIN testuser1 ;
CREATE LOGIN testuser2 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser2 FOR LOGIN testuser2 ;
--test
execute as login = 'testuser1'
select suser_name()
--cannot execute: EXECUTE permission denied on object 'myPROC', database
'testcert', schema 'dbo'.
EXEC myPROC
revert
--
--9. Grant EXECUTE perms on the proc to users in the User DB
GRANT EXECUTE ON myPROC TO testuser1
--test again
execute as login = 'testuser1'
select suser_name()
--now can execute
EXEC myPROC
revert
execute as login = 'testuser2'
select suser_name()
--but others cabnnnot until granted perms on the proc
EXEC myPROC
revert
--Clean UP
USE testCert
GO
DROP USER testuser1
DROP LOGIN testuser1
GO
DROP USER testuser2
DROP LOGIN testuser2
GO
DROP PROC myPROC
GO
DROP USER myViewUser
DROP LOGIN myViewUser
GO
DROP CERTIFICATE myCERT
GO
USE master
GO
DROP LOGIN myAuthLogin
DROP CERTIFICATE myCERT
GO
DROP DATABASE testcert
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO
--EXEC xp_cmdshell 'DIR "D:\SQLDATA\MSSQL.1\*.cer"' ;
--EXEC xp_cmdshell 'del "D:\SQLDATA\MSSQL.1\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
GO
EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;After further searching I found a cleaner way to accomplish what I need to
do. See example below.
Although I am not entirely sure why this line is necessary:
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
/*
Laurentiu Cristofor's blog
SQL Server 2005: procedure signing demo
http://blogs.msdn.com/lcris/archive.../15/429631.aspx
*/
create database demo;
use demo;
CREATE LOGIN alice WITH PASSWORD = '!Pa55word!' ;
CREATE USER alice FOR LOGIN alice
-- create a procedure that selects from sys.dm_exec_connections
create procedure sp_CreatePrincipal
AS
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
go
-- now use this newly added procedure
-- to create a low privileged principal
EXEC sp_CreatePrincipal
-- we'll now want alice to be able to use the procedure and create new
principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;
-- verify that alice cannot create principals
execute as login = 'alice';
EXEC sp_CreatePrincipal
revert;
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@.))%';
-- create a certificate to sign the procedure
create certificate certSignCreatePrincipal with subject = 'for signing
procedure sp_CreatePrincipal';
-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
-- backup certificate to file; it will be used later to put the certificate
in master
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';
-- create and map a user to the certificate
--? create user u_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file =
'certSignCreatePrincipal.cer';
-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
GRANT VIEW SERVER STATE TO l_certSignCreatePrincipal;
-- we're done!
use demo;
-- check that the certificate in demo matches the one in master
select c.name
from sys.certificates c
JOIN master.sys.certificates mc ON c.thumbprint = mc.thumbprint;
-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal
revert;
-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
EXEC sp_configure 'xp_cmdshell', 1 ;
RECONFIGURE ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\certSignCreatePrincipal.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
use master;
drop certificate certSignCreatePrincipal;
drop database demo;|||Dave (Dave@.discussions.microsoft.com) writes:
> I am trying to avoid the "Permission denied" error message when a proc
> tries to select records from one of the system tables in a SQL Server
> 2005 master database.
> Sepcifically I need to be able to capture the IP address of the calling
> user which I can get from sys.dm_exec_connections.
> I have struggled with this for some time and could not find anything
> directly on point in MSDN. However, I was able to cobble something
> together that "appears" to work as shown below.
> I am hoping that someone who has specific experience with this issue
> might be able to comment on my approach and point out any issues or
> deficiencies.
Yes, you have to do something you did, although you don't the EXECUTE
AS thing. Certificate signing is all you need. I have an article on
my web site that show how you can do this:
http://www.sommarskog.se/grantperm.html
Since you seem to have grasped some of this already, you may want to
rush through until the first BULK INSERT example.
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|||Thanks Erland Sommarskog.
That is a very useful web site at http://www.sommarskog.se/
However, one thing I am not clear on; why do you back up the private key and
Laurentiu drops it? What is the risk of dropping it like he did?
Erland Sommarskog's example:
BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')
Laurentiu Cristofor's example:
alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';|||Dave (Dave@.discussions.microsoft.com) writes:
> That is a very useful web site at http://www.sommarskog.se/
> However, one thing I am not clear on; why do you back up the private key
> and Laurentiu drops it? What is the risk of dropping it like he did?
>
> Erland Sommarskog's example:
> BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
> WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
> ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
> DECRYPTION BY PASSWORD = 'All you need is love')
>
> Laurentiu Cristofor's example:
> alter certificate certSignCreatePrincipal remove private key;
> backup certificate certSignCreatePrincipal to file =
> 'certSignCreatePrincipal.cer';
If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)
I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.
I should definitely spend some time reworking the example - I only
need to find that time. :-)
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